Combo box selection populates form

[sstasiak]

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

[MSeda]

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.

Private Sub Combobox1_KeyDown(KeyCode As Integer, Shift As Integer)
 
If KeyCode = 13 Then  '13 is the enter key so the event only occurs when the enter key is pressed
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
Docmd.close acform, me.form.name  'close the search form
End If
 
End Sub

[sstasiak]

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:

Private Sub Combobox1_KeyDown(KeyCode As Integer, Shift As Integer)
 
If KeyCode = 13 Then  '13 is the enter key so the event only occurs when the enter key is pressed
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
Docmd.close acform, me.form.name  'close the search form
End If
 
End Sub

Some changes that I think should be made for my specific problem is:

Change:

Private Sub Combobox1_KeyDown(KeyCode As Integer, Shift As Integer)

To:

Private Sub Combo8_KeyDown(KeyCode As Integer, Shift As Integer)

Then change:

Docmd.openform “OncRegMain”, ,,”[MEDREDNO] =  ” & me.Combobox1

To:

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:

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?

[NeoPa]

...
When I do this and test it, I get a syntax error on the following line:

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.