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

Combo box selection populates form

P: 2
Hello all

I have read similar threads on this site, but can't seem to get any of the solutions working for me.

I have a main form(OncRegMain) that adds records to a table(tblOncReg). This table has a primary key(MEDRECNO).

What I want to do is create a search form with a combo box containing field columns: MEDRECNO, LNAME, and FNAME from tblOncRegMain. When the appropriate record is selected from the combo box, I want to be able to hit enter and have the form OncRegMain open with all the fields in the form populated with the data associated with the combo box selection.

I know how to create the combo box with the appropriate columns, but have no idea what to do for the "On Enter" action so that for OncRegMain opens with the fields populated.

Any ideas?

Thanks in advance
Feb 22 '07 #1
Share this Question
Share on Google+
3 Replies


MSeda
Expert 100+
P: 159
Firstly, the "on Enter" event occurs when the user first enters the control not when the user presses enter while in the control.
you want to use the key down event, this event occurs whenever the user presses any key while in the control.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combobox1_KeyDown(KeyCode As Integer, Shift As Integer)
  2.  
  3. If KeyCode = 13 Then  '13 is the enter key so the event only occurs when the enter key is pressed
  4. Docmd.openform “OncRegMain”, ,,”[MEDREDNO] =  ” & me.Combobox1   'open the form where medrecno matches the record in your combobox assuming medrecno is the bound column of the combobox otherwise refer to its column
  5. Docmd.close acform, me.form.name  'close the search form
  6. End If
  7.  
  8. End Sub
Feb 22 '07 #2

P: 2
MSeda

Thanks for the reply. I'm still having problems though. Here's exactly what I'm doing....maybe you can see where I'm going wrong.

In a blank form I added a combo box that includes fields: MEDRECNO, LNAME, and FNAME. Sort ascending by LNAME. Combo box label is MEDRECNO. Listed as 'Combo8' in the property sheet.

I click on the combo boxs' dropdown(which says 'unbound'), and go to code builder for the 'On Key Down' event. Then I paste this in:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Combobox1_KeyDown(KeyCode As Integer, Shift As Integer)
  2.  
  3. If KeyCode = 13 Then  '13 is the enter key so the event only occurs when the enter key is pressed
  4. Docmd.openform “OncRegMain”, ,,”[MEDREDNO] =  ” & me.Combobox1   'open the form where medrecno matches the record in your combobox assuming medrecno is the bound column of the combobox otherwise refer to its column
  5. Docmd.close acform, me.form.name  'close the search form
  6. End If
  7.  
  8. End Sub
Some changes that I think should be made for my specific problem is:

Change:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combobox1_KeyDown(KeyCode As Integer, Shift As Integer)
To:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo8_KeyDown(KeyCode As Integer, Shift As Integer)
Then change:
Expand|Select|Wrap|Line Numbers
  1. Docmd.openform “OncRegMain”, ,,”[MEDREDNO] =  ” & me.Combobox1
To:
Expand|Select|Wrap|Line Numbers
  1. Docmd.openform “OncRegMain”, ,,”[MEDREDNO] =  ” & me.Combo8
For the above line, MEDRECNO is the first column in the combo box, and the bound column is set to 1. Does that mean that MEDRECNO is the bound column?

When I do this and test it, I get a syntax error on the following line:
Expand|Select|Wrap|Line Numbers
  1. Docmd.openform “OncRegMain”, ,,”[MEDREDNO] =  ” & me.Combobox1   'open the form where medrecno matches the record in your combobox assuming medrecno is the bound column of the combobox otherwise refer to its column
When I first paste the whole series of code, the above line shows up in red(don't know what that means). It also highlights [MEDRECNO] on that line and says Compile Error: "Expected end of statement". Is the syntax correct or should there be quotes removed or added somewhere?
Feb 22 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
...
When I do this and test it, I get a syntax error on the following line:
Expand|Select|Wrap|Line Numbers
  1. Docmd.openform “OncRegMain”, ,,”[MEDREDNO] =  ” & me.Combobox1   'open the form where medrecno matches the record in your combobox assuming medrecno is the bound column of the combobox otherwise refer to its column
You still refer on this line to MEDREDNO (Not MEDRECNO) and Combobox1 (Not Combobox8).
Also, if you look carefully, you'll see the string quotes (") are not really (") at all but similar characters which won't work as quotes in VBA.
Feb 26 '07 #4

Post your reply

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