Data Selection and going to related record

[razjafry]

Hi,
I am not that proficient in VBA but need to complete this small database.
I am creating a form to search the individual info
I need to have a text box to enter the last name with auto fill once you enter one or two letters.
Then I need all those records showed under that/those letters to show up in the list box.
Then by double clicking any of the record from the list box we shoud be able to go to that specific record from the other form.
At the bottom a button with name Select need to do the same operation as double clicking from list box.
I really appreciate your help in this regard.
Thanks,
Syed

[zaidlig]

Can suggest an auto correcting combo box instead of a list box. First add the combo box (mine is Combo60) to your form define the rowsource as the list of items you want to choose from (you can use a query, table just about anything) make sure "limit to list" is yes. Leave control source blank. Now you have a self correcting way of getting to your record - as you type it will bring up matching items and if you pull down it will show all the items close to your typing.

Next add the code:

Private Sub Combo60_AfterUpdate()
'Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[Standard] = '" & Me![Combo60] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Where [Standard] is the column name of the forms Record Source and Me![Combo60] is the selected value of the combo box. The cool thing is if you base the rowsource on a select unique query the combo box will always be a pull down of the records in the table you are trying to search and you don't need a separate row source table for your pull down.

[razjafry]

I really appreciate your answer...
somehow its not working and not taking me to the related record of the table/form.
Would you please help me little further by adding more steps to get the job done.
(I am using Access 2000/2002)
I got the combo60 with the desired rows. I need either to select the name from that combo pull down list by double clicking it or pressing ENTER or by selecting a SELECT command button at the bottom.
The name of the form to show the related record is "frmCcondensed".
I am confused how that selection will take me to that specific record in the form.
With the wizard help, it takes me to the start of the form not to that specific record.
I might be asking little too much but your help will save me by all means.
Thanks and regards,
Syed

[zaidlig]

I really appreciate your answer...
somehow its not working and not taking me to the related record of the table/form.
Would you please help me little further by adding more steps to get the job done.
I got the combo60 with the desired rows. I need either to select the name from that combo pull down list by double clicking it or pressing ENTER or by selecting a SELECT command button at the bottom.
The name of the form to show the related record is "frmCcondensed".
I am confused how that selection will take me to that specific record in the form.
With the wizard help, it takes me to the start of the form not to that specific record.
I might be asking little too much but your help will save me by all means.
Thanks and regards,
Syed

Ok in my Example Combo60 is a control on the frmCcondensed. If you add the Combo60 to your frmCcondensed it will move the form to the selected record.

If you want form A to open form B based on the selected value of Combo60 then add Combo60 to form A and use this code:

Private Sub Combo60_AfterUpdate()
DoCmd.OpenForm "form name B", acNormal, , "[record ID]= '" & Me![Combo60] & "'", acFormEdit, acWindowNormal
End Sub

Where [record ID] is the field name of the unique ID in the datasource of form B.

[razjafry]

Again thanks,
now I am getting runtime error 2501 with message OpenForm action was canceled when I use intContactID in the code.
I am giving here all the steps I am taking:
I am creating a form with a combo box (named as combo60) and using intContactID, strLName, strFName from tblContact as row source (selecting hiding the first key column- intContactID in the wizard as recommended).
Then I am adding the code given by you:

Private Sub Combo60_AfterUpdate()
DoCmd.OpenForm "frmCcondensed", acNormal, , "[intContactID]= '" & Me![Combo60] & "'", acFormEdit, acWindowNormal
 
End Sub

If I use strLName in the code instead of intContactID then it takes me to the empty boxes of frmCcondensed without any option otherthan filling the info in
instead of getting to the desired result selected form combo box.

I hope this will help you to figure out how to help me :)
Thanks a lot,
Syed

[Minion]

Try this code I think you'll find it works for you with a little tweaking.

Private Sub cmbLastName_AfterUpdate()
    DoCmd.OpenForm "<<target form name>>", , , "<<field name>> = '" & Me.cmbLastName & "'"
End Sub
 

<<target form name>> - thsi will be your target form
<<field name>> - this is your field name. This should be buffered by quotes without brackets.

So putting it all together if the form is called formTwo and the table field is called LastName. Then the code would look something like:

Private Sub cmbLastName_AfterUpdate()
     DoCmd.Openform "formTwo", , , "LastName = '" & me.cmbLastName & "'"
End Sub
 

Hope this helps.

- Minion -

[zaidlig]

Again thanks,
now I am getting runtime error 2501 with message OpenForm action was canceled when I use intContactID in the code.
I am giving here all the steps I am taking:
I am creating a form with a combo box (named as combo60) and using intContactID, strLName, strFName from tblContact as row source (selecting hiding the first key column- intContactID in the wizard as recommended).
Then I am adding the code given by you:

Private Sub Combo60_AfterUpdate()
DoCmd.OpenForm "frmCcondensed", acNormal, , "[intContactID]= '" & Me![Combo60] & "'", acFormEdit, acWindowNormal
 
End Sub

If I use strLName in the code instead of intContactID then it takes me to the empty boxes of frmCcondensed without any option otherthan filling the info in
instead of getting to the desired result selected form combo box.

I hope this will help you to figure out how to help me :)
Thanks a lot,
Syed

Verify that the Combo60 is bound to column 1 it appears that its bound to column 2.

The row source data must be in the order you indicated "intContactID, strLName, strFName" with the widths being 0;#;# where # is any number and bound to column 1.

Next verify the form you are opening will open normally outside of the use off Combo60. Finally change your code to:

DoCmd.OpenForm "frmCcondensed", acNormal, , "[intContactID]= '" & Me![Combo60] & "'"

Remember "[intContactID]" must a field name in the datasource of the form you are opening.

[razjafry]

Thanks a lot Zaidlig, (and also thanks to Minion for your kind reply)
Its working fine now with the only note that before it was bound to 1 and when I changed it to bound 2 and changed strLName in the code instead of intContactID it started working instantly.
I really appreciate your kind help.
Syed

[razjafry]

Hi,
Now to the next level of the problem...
I need to search a person by Last Name, City, Riding.
I 'll be using toggle box for these three boxes on the top
but from there on I am blank and need same kind help as I got before.
With two possible scenrios
1) All three search have the source- same single table
2) These three searches have different sources i.e depending on different tables/forms/queries.
Please accept my applology if I am asking too much.
Help will be highly appreciated.
Thnaks,
Syed