Code not working

[sstasiak]

Hello all

I don't know VBA at all, but I got a piece of code to use for an issue I was having, and I'm not sure if the syntax is correct.

Here's the code:

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

When I paste this in, the line with [MEDRECNO] is highlighted in red. 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?

Any advice is greatly appreciated.

thanks

 

[KernelK]

Write your Docmd.Openform like this:

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

Note what type of value is being held in the field [MEDREDNO]. If it is a text value, you will need quotation marks in the wherecondition like this:

Docmd.openform “OncRegMain”, wherecondition:= ”[MEDREDNO] = '” & me.Combobox1 & "'"

 

[Peter Bellamy]

This is the example in Access 2k Help:
DoCmd.OpenForm "Employees", , ,"LastName = 'King'"

The where clause, your [MEDRED], has to equal something, Null ("") or a parameter.

And [MEDRED] must be available from the Forms underlying Table or Query, or a field on the form.

Hope that helps

 

[sstasiak]

Thanks for the replies.

Kernel

I tried what you suggested. When I select something from the combo box, and hit enter, the form OncRegMain opens, but no fields populate. I also get a VB runtime error '2465': Application-defined or object-defined error.

When I debug, the line with 'DoCmd.Close' is highlighted

Peter

I'm not really understanding what you mean. Remember, i'm a rookie and have no experience with VB code so I need to have my hand held through this so to speak.

Here's the code exactly how it appears on my screen:

Private Sub Combo8_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", wherecondition:="[MEDRECNO] =  " & Me.Combo8
DoCmd.Close acForm, Me.Form.PatientSrch  'close the search form
End If

End Sub

 

[KernelK]

Instead of:

DoCmd.Close acForm, Me.Form.PatientSrch

Use:

DoCmd.Close

It accomplishes the same thing you were trying to do, close the form the code is in.

Now at least our Wherecondition isnt causing an error now. What Peter Bellamy was trying to convey is that your form "OncRegMain" must be bound to the table/query that contains the field [MEDRECNO]. In other words, the recordsource property for the form "OncRegMain" must be set to a table or query that the field [MEDRECNO] is a part of. Otherwise, the form cannot restrict the entries to our requirement of [MEDRECNO] = anything, because the form will not know what [MEDRECNO] is. Check that out first, if that is set properly, look at the table properties and tell us the Type for the filed [MEDRECNO]

 

[RoyVidar]

I'll put in a little disagreement there.

Unqualified close, will close the object having focus, I think. When doing

docmd.openform "someform"
docmd.close

you'll risk to close the form you open...

Much better, in my view, to qualify - but the second arguement should be the object name.

docmd.close acform, me.name

 

[KernelK]

I don't believe the focus to be important, it is the Calling object that is affected. But according to Access Help:
"If you leave the objecttype and objectname arguments blank (the default constant, acDefault, is assumed for objecttype), Microsoft Access closes the active window. If you specify the save argument and leave the objecttype and objectname arguments blank, you must include the objecttype and objectname arguments' commas."
So that would leave me to believe that you may be right. I've used this method in this way for 10+ DBs and have yet to encounter that problem, but theres no harm in qualifying anyway.

 

[RoyVidar]

Reading a bit closer, I don't think the combos value is populated when using the keydown event, which mean that is the reason no records pop up in the opened form.

Try changing to the after update event of the combo in stead.

DoCmd.OpenForm "OncRegMain", , , "[MEDRECNO] = " & Me.Combo8
DoCmd.Close acForm, Me.name

 

[KernelK]

Good catch Roy

 

[sstasiak]

Kernel

Here are some updates:

1. When I use DoCmd.Close, I think it's applying that command to the form that was opened in the previous line because it opens form OncRegMain and closes it righ away. I took DoCmd.Close out just to see if the form was populating, and it wasn't....but it did stay open, along with the search form.

2. Record source for form OncRegMain is tblOncReg which has MEDRECNO as its primary key. The type for the field MEDRECNO is 'number'. Not sure if this matters, but the input mask is: !00000000;

Also, tblOncReg is linked to 2 other tables via MEDRECNO. I have no idea if any of this other stuff I posted would affect what I'm trying to do, but I figured I'd add as much info as possible.

 

[KernelK]

Looks good, and it looks like Roy should be right on both counts.

 

[sstasiak]

Thanks for being so quick with the responses guys. This is farther than anyone's been able to get me so far, but it's not quite all the way there yet. I took the DoCmd.close out for now because it kept giving me errors no matter which of your ways I tried.

***Correction*** Using the code that Roy posted, the form OncRegMain does not open. It only opens using the code from Kernel

So Kernels code is the one that is opening the form. Roys gives an error...see below.

Here's the code again as Roy instructed:

Private Sub Combo16_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", , , "[MEDRECNO] = " & Me.Combo16
End If
End Sub

The code with the DoCmd.Close that is giving me an error is:

Private Sub Combo16_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", , , "[MEDRECNO] = " & Me.Combo16
DoCmd.Close acForm, Me.PatientSrch
End If
End Sub

When I hit enter with the code above, I get a Compile error: "Method or data member not found" and the debugger opens with "PatientSrch" highlighted.

 

[sstasiak]

The only thing that's even coming close is using the following code in the "On Key Down" event, but there's still no data being populated.

Private Sub Combo16_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", wherecondition:="[MEDRECNO] =  " & Me.Combo16
End If

End Sub

 

[KernelK]

Take the code that I gave you, minus the DoCmd.Close (worry about that later) and place it into the combo boxes AfterUpdate event as Roy suggested. When you have the code in the Keydown event, the combobox's value is not yet set, so you are always passing "" or nothing as your where condition. If you want to check this out before you move the code, then follow these steps (it will help to learn to debug other things too):

1. In the code window, put your cursor on the line:

DoCmd.OpenForm "OncRegMain", wherecondition:="[MEDRECNO] =  " & Me.Combo8

2. Press F9 on the keyboard. The entire line should be highlighted red now. This is called adding a breakpoint, now when the code executes, it is going to stop on this line before proceeding.

3. Test the form by entering something in the combo box and hitting enter. Your code window should pop up with our breakpoint highlighted in yellow.

4. Put your mouse over Me.Combo8 and a tooltip should appear and tell you the value of Me.Combo8 at this time. If it says something like: Me.Combo8 = "" Then Roy was correct and you will need to move the code. If it has the value you typed in, well, we have some more work to do then.

5. If the tooltip does not appear when you put your mouse over Me.Combo8 then goto the menu at the top, to View, then click Immediate Window. In the new Immediate Window that comes up type: ?me.combo8
The value for me.combo8 should be displayed on the next line. If nothing appears, then the value is nothing and so on.

6. Code execution is continued by pressing F5 or the menu Run, Continue

Edit: To turn off the breakpoint, put your cursor back on the line and hit F9 again.

 

[sstasiak]

OK Kernel, I got it working, but here's some strange things that might explain why it wasn't working before.

I followed your debugging steps(thanks for those by the way....probably would've been tough to figure out had I not done that). I selected a record from the combo box, hit enter, and the code popped up with the yellow highlight. When I moused over Me.Combo16, it said "Me.Combo16 = 14" instead of the 8 digit Medical Record number[MEDRECNO]. tblOncReg has an additional autonumber ID field, but it's not the primary key. The ID field was being automatically selected in the combo box wizard and I didn't know why(because it's an autonumber and Access thinks it's a primary key maybe???).

What I did was I changed the wherecondition to ID instead of MEDRECNO, and it's working. The logic behind it is still a little confusing to me, but I'll take a look at it again tomorrow when I'm not so fried.

If anyone can explain the logic behind that, I'd love to hear an explanation.

Again, tblOncReg has MEDRECNO as a primary key, and also has an autonumber ID field. Me.Combo16 was being set to the ID associated with the selected MEDRECNO. ID was also automatically selected as a field in the combo box wizard, but wasn't showing up as a column in the actual list.

Thanks again for all the help.

 

[sstasiak]

Now, do you guys have any ideas on how to get that search form to close when I hit enter?

I tried macros to close the form on relevant events, but none seem to be working.

 

[KernelK]

Is the form OncRegMain only ever opened from the search form? Because if it is, you can just close the search form in OncRegMain Load event.

 

[sstasiak]

OncRegMain is opened from more than just the search form, but wouldn't that work either way? **Just tried it and it works.

What I'd like to try is to put that same search combo box on the top of the OncRegMain form so that users can jump from patient to patient without going back to the switchboard to get to the search form.

I'm trying to modify the code you gave me but not sure of the command to use instead of OpenForm. When I use the exact code from the search form, I click on the record in the combo box on form OncRegMain, hit enter, then the form flickers with the right data, but then the fields go blank again. When I click on the combo box again on the same record, then the data populates and stays.

It seems that the OpenForm command might have something to do with it.