AC97: Switchboard help

[KelMcc]

OK, I have a switchboard. On the switchboard I added a combo box that allows you to pick an employee name. I'd like it to work so that when you select a name, say "Jones", then click a form, it filters so that you only see the data for employee "Jones".

I hope that makes sense?

I'd like to do it this way, but am open to other ways to do this.

I'm not a coder so if you post code please also give me specific instructions of where/how to cut/paste it.

Thanks in advance for your help.

 

[Jack Cowley]

Create the command button using the Wizard and one of the options will allow you to open a form at a specific record. Select that option, finish the Wizard and you should be good to go.

 

[KelMcc]

I did use the wizard and as it currently works, that is what happens. But, I've added the "complication" of trying to limit the data that's returned to what I happen to put in the combo box (employee name) that I added to the form.

You do mean the command button wizard? I'll retry. Also, one of the "benefit" I was hoping to realize was that if they didn't enter an employee into the combo box, they would get all the data.

Man, I hope I'm not being too vague.

 

[Jack Cowley]

The Wizard will allow you to open a form at a specific record. Just try the Wizard again.

Once you have the code created by the Wizard then add this code before the StrDoc bit to open the form and show all records if the Combo is left blank....

If IsNull(Me.ComboBoxName) Then
DoCmd.OpenForm "FormName"
Exit Sub
End If

 

[KelMcc]

Yeah, I can see how this wizard wants to help me do what I'm suggesting.

But, I got this error:

"The object doesn't contain the Automation object 'ISIF'."

ISIF is the table that the form I'm calling is tied to. The combo box gets the employees names from a table, BA. The table BA is linked to the ISIF table by name. Now, this is w/out that extra code you suggested, but that is for when the combo box is empty, which is wasn't in this case.

 

[Jack Cowley]

You have not posted the code so it is hard to tell what you have done. Simple code like this in the command buttons On Click [Event Procedure] should work:

DoCmd.OpenForm "YourFormName", , ,"[EmployeeName] = '" & Me.ComboBoxName & "'"

The assumption is that the Combo box's bound field is the Employee Name and that EmployeeName is a field on the form you are opening. Table names should have no bearing on this.

 

[KelMcc]

ok, I tried this:

DoCmd.OpenForm "[ISIF Form]", , ,"[BA] = '" & Me.BA & "'"

ISIF Form=That the forms I want
BA=employeefield
BA=also is the name of the combo box.

I now get this error:
"Microsoft Access can't find the macro 'DoCmd.'

I did cut/paste the original code from that field that was generated by the wizard. Its in the attachment.

NOTE: in the attachment BA is called "combobox22". That's not the source of the error, as all my names are in sync now.

 

[KelMcc]

Jack, I figured it out! The wizard was working just fine! I just found some extraneous garbage hanging about that I had to clean up.

Thanks again!

Case CLOSED!

 

[Jack Cowley]

Glad you got it sorted!

 

[KelMcc]

Doh! Spoke too soon! Well sort of. The basics work. I can get the filter predicated on the employee that is selected. The problem is when I leave the employee blank, it returns nothing.

You did predict this when you said:

<Once you have the code created by the Wizard then add this code before the StrDoc bit to open the form and show all records if the Combo is left blank....

If IsNull(Me.ComboBoxName) Then
DoCmd.OpenForm "FormName"
Exit Sub
End If >

Question: When you say "before the StrDoc bit" where do I find that? Is that somewhere in the [Event Procedure] code?

 

[Jack Cowley]

Dim stDocName As String
Dim stLinkCriteria As String

If IsNull(Me.BA) Then
DoCmd.OpenForm "ISIF Form"
Exit Sub
End If

stDocName = "ISIF Form"

stLinkCriteria = "[BA]=" & "'" & Me![Combo22] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Be sure my names are correct for the form and combo box.

 

[KelMcc]

Perfect! My hero!