By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,844 Members | 1,889 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,844 IT Pros & Developers. It's quick & easy.

Data Selection and going to related record

P: 30
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
Jan 10 '08 #1
Share this Question
Share on Google+
8 Replies


P: 45
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo60_AfterUpdate()
  2. 'Find the record that matches the control.
  3.     Me.RecordsetClone.FindFirst "[Standard] = '" & Me![Combo60] & "'"
  4.     Me.Bookmark = Me.RecordsetClone.Bookmark
  5. 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.
Jan 10 '08 #2

P: 30
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
Jan 11 '08 #3

P: 45
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo60_AfterUpdate()
  2. DoCmd.OpenForm "form name B", acNormal, , "[record ID]= '" & Me![Combo60] & "'", acFormEdit, acWindowNormal
  3. End Sub
Where [record ID] is the field name of the unique ID in the datasource of form B.
Jan 11 '08 #4

P: 30
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo60_AfterUpdate()
  2. DoCmd.OpenForm "frmCcondensed", acNormal, , "[intContactID]= '" & Me![Combo60] & "'", acFormEdit, acWindowNormal
  3.  
  4. 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
Jan 11 '08 #5

Minion
Expert 100+
P: 108
Try this code I think you'll find it works for you with a little tweaking.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbLastName_AfterUpdate()
  2.     DoCmd.OpenForm "<<target form name>>", , , "<<field name>> = '" & Me.cmbLastName & "'"
  3. End Sub
  4.  
<<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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbLastName_AfterUpdate()
  2.      DoCmd.Openform "formTwo", , , "LastName = '" & me.cmbLastName & "'"
  3. End Sub
  4.  
Hope this helps.

- Minion -
Jan 11 '08 #6

P: 45
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo60_AfterUpdate()
  2. DoCmd.OpenForm "frmCcondensed", acNormal, , "[intContactID]= '" & Me![Combo60] & "'", acFormEdit, acWindowNormal
  3.  
  4. 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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmCcondensed", acNormal, , "[intContactID]= '" & Me![Combo60] & "'"
Remember "[intContactID]" must a field name in the datasource of the form you are opening.
Jan 11 '08 #7

P: 30
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
Jan 11 '08 #8

P: 30
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
Jan 11 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.