help me with okay button

i have created a form like

first name ............. last name.............. candidate id.............

and an option box here with listing of six forms

() candidate profile form
() candidate evaluation form
() candidate phone internview evaluation form
() candidate technical interview evaluation form
() candidate etc etc

i want when the user typed in first name or last name and select
any of the form listed (only one can be selected out of six forms)
and press the OK button.. the respective form of that candidate
should open

help me please

You are on the right track. An option group will allow only one selection, so you can use that to determine which form to open using a Select Statement.

Select Case OptionGroup

Case 1
DoCmd.OpenForm "Candidate Profile"
Case 2
DoCmd.OpenForm "Candidate Evaluation"
Case 3
... etc
End Select

Obviously use the name of your option group and forms in place of the example above and place the code in the OnClick event of your okay button.

lwells

i understand what you have written about the query...about selecting
the form

but what i really need is .. when the user type in the first name
or the last name and select one of the respective form.
the selected form along with the candidate data should show up

will it be possible.

thanks

If your forms record source is set to the table that holds the candidate data, then you would include a filter as an example below using the last name as a filter:

DoCmd.OpenForm "Candidate Profile", , ,"LastName = '" & Me.txtLastName & "'"

This is a sample only, but it will give you the methods how to do it.

lwells

yes, the each form has it own table like to say

about one form : View Candidate Form

__________________________________________________ __________
candidate id .................... first name............. last name ................
(these are the fields from table : main_table)
_____________which form would you like to view ? ____________

() candidate profile form
(which is another form which has main_table + work experience table + skill set table attached to this form)

() Resume Evaluation form
(this is another form which has resume evaluation table attached to it)

() recruiting progress tracker form
(this is another form which has recruiting progress tracker table attached)

() Phone Interview Evaluation form
(this is another form which has few fields from recruiting tracker table attached)

() Technical Test Evaluation Form
(this is anothr form which h has few fields from recruiting tracker table attached)

() Personal Interview Evaluation Form
( this is another form has few fields from recruiting tracker table attached)

__________________________________________________ ____________________________

Now this is the layout of the form... and OK and Cancel button below


I want when candidte fill in the first name or the last name and choose one of the form from the list (which is option boxes) and press the ok button... the form should open up of the respective candidate

Okay, here you go

Private Sub cmdOK_Click()
On Error GoTo Err_Handler
Dim stDocName As String
Dim strFirstName As String, strLastName As String
Select Case OptionGroupName
Case 1
stDocName = "candidate profile"
Case 2
stDocName = "Resume Evaluation"
Case 3
stDocName = "recruiting progress tracker"
Case 4
stDocName = "Phone Interview Evaluation"
Case 5
stDocName = "Technical Test Evaluation"
Case 6
stDocName = "Personal Interview Evaluation"
End Select

If Not IsNull(First_Name) Then
strFirstName = First_Name.Value

If strFirstName = DLookup("[First_Name]", "main_table") Then
DoCmd.OpenForm stDocName, , , "[First_Name] ='" & strFirstName & "'"
Else
MsgBox "No Record Was Found With " & _
"The First Name Of " & Chr(13) & _
Chr(13) & Chr(9) & Chr(9) & Chr(34) & strFirstName & Chr(34)
End If

ElseIf Not IsNull(Last_Name) Then
strLastName = Last_Name.Value

If strLastName = DLookup("[Last_Name]", "main_table") Then
DoCmd.OpenForm stDocName, , , "[Last_Name] ='" & strLastName & "'"
Else
MsgBox "No Record Was Found With " & _
"The Last Name Of " & Chr(13) & _
Chr(13) & Chr(9) & Chr(9) & Chr(34) & strLastName & Chr(34)
End If

ElseIf IsNull(First_Name) And IsNull(Last_Name) Then
MsgBox "Must Enter Either First Name or Last Name To Open A Form"
End If
Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description & " " & Err.Number
Resume Exit_Handler

End Sub

Just make sure you use the correct names of your forms/tables and your fields in the code.

lwells

hi iwell, one of my friend has given me an url wherein i am keeping that file


you can easily download it by saving it to hard disk

the url is
www.sgnco.com/forms004.zip

unzip it and see to the flow of the program

i have had cut and pasted the code you have wrntten
it says object 424 not found

i am so thankful to you

when you unzip it and see to the file.. its access 2003 file

let me explain the flow of the program

in the form section

if you click on 1_mainform

and maximise it

the first one is search >> it will show you a dialogue box with all the fields which are from search_table and this table has Candidate_ID as a primary key which is common in each and every other table thruout the application.
In this i want when any of the fields get filled up with data from the user and the user press the Go button... the query should show up the required data.

second is add a new record >> which shows the candidate profile form (as already said : this form includes main_table and work experience as sub form which you can easily see in design view. I want when ever the user press the add a new record... it should show up the blank form with latest candidate_ID

i have had those code which you have given me fro starting auto number and concatenating it with SAG_

third is edit an existing record >>
which is again the same form which ask for first name or last name or candidate id and then selectio of form which you want to open and edit the same.

fourth is view candidate data >>

which is same as above.. but the form are only for browsing, no changes can be done.

can you give me your email id... so that i can post the same file to you also...

last is Printout form >> which is again a selection of form... multi section and print buttion

last is exit button.

If you happen to see rest of the form in design view.. there are other form also ... like candidate technical test form, resume evaluationform, etc etc... which are like separate forms from where only one or two fields are required for the database which is again connected to main_table .

anything more required... please let me know.

Hi ranbir,

I took a look at your database, and I would like your permission to modify your table design and structure. In order to make things work like you are wanting, I really need to normalize the tables and set up relationships properly in order to make the code work properly. Otherwise, the code that I would have to write to overcome those issues, would be very inefficient (least to say very bad programming techniques) and most certainly would cause you to maintain the database on a regular if not daily basis.

lwells

Hi ranbir,

I went ahead and corrected your tables and relationships and added the code to make your forms work. You will need to finish some of the forms by adding the placement of the controls. The forms already have the necessary list completed, so it is just a matter of selecting where the controls go on your forms.

You search form will need to be narrowed down. Or at least select by category of some sorts. Search forms need to be broad in nature and not so detailed as you have constructed the form. It will make it a lot easier on the user to limit the choices. Once you have reconstructed your search form, I can give you the necessary code to make it work correctly. A search form is not bound to a table or query, it must be left unbound and then build the sql criteria from the selections the user entered. You will need to decide how you want the results of the search displayed, as a query view or as another form.

I have entered one sample record for testing. The database was compiled/compacted.

I will send the database to your email account.

lwells

First of all, I would like to thank you for the efforts you did on your parts for my database. I have no words for your such heart rendering work. Today while coming to the office, i was praying to the God, hey God Please help me with the database form, or my boss would eat me raw. And here in the office, the very first thing i did open this forum and was amazed to see your comments. I have downloaded the file and seeing to it. I will let you know if any changes required or else i too would look thru the codes and will do the necessary changes if required, in the duplicate copy.

Although, this database desining is not my work. I am in 3d animation and graphics job. We are group of five peoples in all 5 animators. Our is a startup company. My boss just asked me ranbir can you do database design. I said yes i can... and did all the database desinging . leaving out the coding... I even told him that i am no proficient in coding. After a week period, when everything with design was over, he said ranbir you go for coding tooo, becuase we have not enough work for the time being and it would be good of you to look thru net etc etc and try your best. I did my best thru navigation but was not able to do the advance part which you did for me. (i was jobless for 2 years, not earning a single penny all thru years, so thought of doing this at a great risk).

May God bless you with wonderful life with all worldly pleasure and wealth.

I am from india. If you need anything from here... please dont hesitate to ask me. I will do all my best to get the stuff for you...

Bye for now and thanks a lot....

first modification :

if i delete all the records in the table and start a fresh one...
te new record get 1005 number

where is the setting for making the number to start from 1000.


In the edit and view records you have given a-z alphabets...where are the codes for these A-Z alphabets in macros.

Rest for the time being... its functioing great.... its really wonderful of you that you are able to understand my need.

Rest i will let you know about the search form... although the search criteria is a big one... but the report or the result of the query are only few columsn...
like to say ...first name, last name, mobile bumber, email id and etc etc... only limited fields

thanks so much....

Hi ranbir,

What I recommend, is to do this as the very last step. You will need to add lots of records for testing...So go ahead, add lots of test records..the more the better (100 or more) and leave them there for testing the entire application. Then when you are finished building all your queries, forms and reports and are ready to actually use the application, delete all test records and just run the query you already have. I changed that query to reset the autonumber in this table. Just type the number 999 when the message box opens, delete the blank record and now you will start at 1000.

On the Alpha list, I seldom use macros, but you can find the code behind the OnClick event...not much to it really...nothing more than an option group with the numbers 1 to 26 and then add 64 to get the alpha character and make a search using find first method. I use this method as it is a lot faster for the user than to have them type in a name, and then run into misspelling issues or duplicate names etc. Its hard to see the benefit with just a few records, but your users will appreciate not having to always type a name. Click a button to narrow the list by alphabet letter, then use a filtered drop down box showing the names by that letter...way faster than typing and much easier to use. By the way, the buttons have already been coded, so you won't need to modify them. The code was commented so you will understand.

lwells

Nothing like that happen... it still continue to add new number SAG_1022 and so on. what I did... first of all i delete every other record from the table and only one row left with (autonumber) left. I did the same with rest of the tables. Run the query and type in 999. and press OK . then to the main menu. I add one record. But the same when adding new first name and last name... its shows new number SAG_1031.

Moreover, when adding a new record, it should not show any navigation key buttons at the bottom. Otherwise, if i press back key and overwrite the existing record like thefirst name and thelast name...the record get copied to that record. Even if, the navigation keys are at the bottom, and the form is blank, when typing in the first name like to say Prashant, (if the name prashant is already in the record, it should give some type of warning like the prashant named already exists, likwise this type of message), or else the person can easy type in the prashant record with new id every time. I tried the same over my end, if you add new record, its give new ID everytime, that's Ok. But what about the person record with the same name. Person can easily overwrite the existing record. I can have prashant resume 5 times in a row. Mean to say something need tobe done so that records are unique in every respect and no duplicates or there should not be optiosn for overwriting etc etc. Is there any way, that the alpha keys A-Z do not shows up in the add record dialogue box. We need them only in edit or view records. Moreover, i typed in the few records with first initial startingwith R and J and when i press the S alpha key, it still shows the R initial names etc etc... It should show the message like "names starting with S does not exit"

When adding new record, the person presses the arrow key and dialogue box with add new record show up. Suppose you dont close this window and press the other arrow button like to say edit or view record, the heading remains the same. this should not happen. One have to close the dialogue box and then everything is fine. I agree that every form has been given blue background color, its seems to be same all over. But the option should be that when the user presses the second button or any other button, the already open window should go off.

One problem is when i have evaluated the person resume and given him overall rating, the values did show up in the text box after calculation. This value is right now in resume evaluation form, I want this value the overall rating should also show up in other form Recruiting progress tracker form in the first column where we have given a box overall resume rating ............... (means one value from one form to to other form field).

For the query button (search box), the query should be like, it should run thru every other text field box or options or radio buttons provided in the search form. Means like to say in my terms, see to whichevery box is click or filled up and run the query related to the values filled up and run the query and the output we need is only first name, last name, mobile number, email id and the latest status in the recruiting progress tracker form.... Do i need to design the form for this output.

first name, last name, mobile number , email id and the latest status of the progress report


i will explain you what does the latest status in the recruiting progress report means :
when the person send his resume to us, we will evaluate his resume and give points on the basis of education , work experience and other things in resume evaluation form. This value then show up in recuriting progress tracker form (how the value fromthis form goes to recruiting tracker form), we then give him a phone call, we conduct phone interview and give him rating, and accordingly we go on checking the radio buttons and filling the text fields provided in the form. We then call him for technical test and accordingly we give him points in the techanical test and any comments in the text box. Rest we then conduct face to face interview and give him offer or either reject him. These are the various stages in the recuriting tracker form. There are probability that the person is rejected outright and we click the radio button in first column of the form-- rejected outright, or may be we call him for phone interview and accept or reject him there or same way all thru the form . There are chances that he get accepted or rejected at any stages. The latest stages in these steps should show up in the query.

Wow ranbir,

Lots of questions here. Okay first, I gather you are not using the database I sent to you, or you haven't copied all the code over. In the code I had placed in the database I sent you handled all the errors in regards to adding new records or editing records including the ability not to modify records when just viewing them and the navigation button issues. If you still have the database I sent you, go back and look at some of the code that is there for handling those situations. Note: the code works only from your main menu, not when opening the form from the database window. Try opening the forms using your main menu for adding new records, for editing records or just for viewing records. In other words, develop the application in the same flow as the user will use the database. The user will not open any form from the database window, so open the forms from the main menu just like the user will do and see if your problem isn't a problem anymore.

As far as when adding a new record and not creating a duplicate, you can add some additional code to look up for existing records using the BeforeUpdate event and then displaying a message box that a record already exists and give the user the option of cancelling or moving to that particular record.

Dim strFirstName As String

strFirstName = FirstName.Text

If strFirstName = DLookUp("[FirstName]", "tblCandidateInfo") Then
If Msgbox ("This is a duplicate Name do you want to go to the record?",vbOKCancel) = vbOk Then
DoCmd.FindRecord FirstName.Text
Else
DoCmd.CancelEvent
End If
End If

For the alpha buttons, to indicate that there are no names with "S" for example, just use the same code and place a message box in place of the system beep.

If Me.RecordsetClone.NoMatch Then
Msgbox "There are no names starting with " & Chr(34) & Chr(RoloGroup + 64) & Chr(34)

In your autonumber issue...after deleting all the records, change the autonumber to a number, compact the database and then change it back to a autonumber and then run the append query to set your number. By the way, all the code, tables, forms, and field names were using the standard naming conventions in the database I sent you. So if you are still using the original database you will need to go back and redo all the code from the database I sent to you, to the table names and field names to the one you are using now.

I was hoping you would use the database I sent to you, as it was normalized and had the relationships set up correctly and all the code was written. All you had to do was finish completing the forms that were still under development and then add any additional code for handling errors as required. All the hard stuff was done. I thought that was why you sent me the database in the first place. Hopefully you are using the database I sent to you, so you won't have to go back and rewrite all the code again.

By the way, to keep things easier for our readers, try posting one question at a time. It will make it a lot easier for others to perform a search on this forum to find answers to their questions that might not get viewed when you combine lots of questions into one post. Also when you post a question, if you have a follow-up question on the same topic, reply back using the same thread. That way others can follow the issue from the beginning and avoid making the same mistake or asking the same question themselves.....Just good rules and etiquette to follow using a forum discussion. If you have a particular coding issue unique to your database that is too complex to post here, send it to my email address and I will look at it.

Cheers,
lwells