Use combobox to select form to open

[gemadan96]

I have a table the list available forms with three feilds (ID, Title, FormName)

I have created a form with a combobox (Combo13) that lists the titles of the available forms.

I have created a button that when clicked it should open the correct form.

I've entered the following code, but it's not working.

Dim strForm As String
strForm = DLookup("FormName", "RegTallyForms", "Title = '" & Forms!RegTallyForms!Combo13 & "'")
DoCmd.Close
DoCmd.OpenForm strForm

Any suggestions?

[pbaldy]

What is the row source and bound column properties of the combo? If I had to guess, the bound column is the ID field, not the Title. I'd also use the column of the combo rather than the DLookup. This isn't exactly what you want, but shows how you refer to the column:

http://www.baldyweb.com/Autofill.htm

[gemadan96]

Changed the bound column and that did the trick. I had tried it with the DIM datatype as integer and used the ID field for the criteria and got nothing but errors.

[gemadan96]

I'm trying to refine this a bit more to reduce the number of forms I have. I've added a field to the reports table called WCond to serve as the where clause for opening the form selected. I added a second DIM statement to declare strWCond and perform a dlook up. I've added the strWCond to the OpenForm command in the correct location, However, when I select a form to open I get an error

Runtime error '2102':

The form name 'EventClass = 'Creative Forms" is mispelled or refers to a form that doesn't esist.

If I simply enter the where condition in the code as "EventClass = 'Creative Forms'" it will work correctly. Right now I have about 20 versions of the form to call from the combobox. I would rather not have to code each one using a bunch of If Then's. If I can get it to call up the where condition from the table that will be much easier if more forms are added in the future.

Here is the latest code

Dim strForm As String
strForm = DLookup("FormName", "RegTallyForms", "Title = '" & Forms![Division Tallies by Registrations]!Combo13 & "'")
Dim strWCond As String
strForm = DLookup("WCond", "RegTallyForms", "Title = '" & Forms![Division Tallies by Registrations]!Combo13 & "'")
DoCmd.Close
DoCmd.OpenForm strForm, , , strWCond

[gemadan96]

I've played around with it some more. The code is as follows:

Dim strForm As String
strForm = DLookup("FormName", "RegTallyForms", "Title = '" & Forms![Division Tallies by Registrations]!Combo13 & "'")
If Me.Combo13 = "All Forms, Weapons, & Self Defense" Then
DoCmd.Close
DoCmd.OpenForm strForm
ElseIf Me.Combo13 = " Please Select..." Then
DoCmd.Close
DoCmd.OpenForm PleaseSelect
Else:
DoCmd.Close
DoCmd.OpenForm strForm, , , "[FWSDRegTally].[EventClass] = '" & strForm & "'"
End If

Using this I no longer receive an error, however if I select something other than "All Forms, Weapons, & Self Defense" or "Please select..." it opens the form, but it is filtered so that nothing is displayed. If I select the EventClass column in the datasheet it shows me all the available text filters, but they are unchecked. The one matching strForm should be checked.

[pbaldy]

Well, the Else clause opens it to a record matching the name of a form. Is that appropriate? I wouldn't think so.

[gemadan96]

That's not appropriate. What I should see a list of all the records matching that event clause.

If I enter the last DoCmd.OpenForm statement as follows:

DoCmd.OpenForm strForm, , , "[FWSDRegTally].[EventClass] = 'Creative Forms'"

I will see the entire list of records under Creative Forms.

But when entered as follows:

DoCmd.OpenForm strForm, , , "[FWSDRegTally].[EventClass] = '" & strForm & "'"

I get an empty list. There has to something in the where condition that's throwing it off.

[pbaldy]

But strForm is the name of a form, is it not? Populated here:

strForm = DLookup("FormName", "RegTallyForms", "Title = '" & Forms![Division Tallies by Registrations]!Combo13 & "'")

I would guess you want something else in the wherecondition.

[gemadan96]

My oversight there. I've added the following:

Dim strWCond As String
strForm = DLookup("WCond", "FWSDRegTallyForms", "Title = '" & Forms![Division Tallies by Registrations]!Combo13 & "'")

And changed the DoCmd.OpenForm to:

DoCmd.OpenForm strForm, , , "[FWSDRegTally].[EventClass] = '" & strWCond & "'"

That now produces the Runtime error 2102 I mentioned earlier.

[gemadan96]

Got it working. Found one more mistake. I had strForm in there twice instead of the second one being strWCond. Fixed and voila!

[gemadan96]

Here's the final code:

Dim strForm, strWCond As String
strForm = DLookup("FormName", "FWSDRegTallyForms", "Title = '" & Forms![Division Tallies by Registrations]!Combo13 & "'")
If Me.Combo13 = "All Forms, Weapons, & Self Defense" Then
DoCmd.Close
DoCmd.OpenForm strForm
ElseIf Me.Combo13 = "All Forms, Weapons Self Defense with >12 Competitors" Then
DoCmd.Close
DoCmd.OpenForm strForm, , , "[FWSDRegTally].[CountOfDivision] > " & 12
Else:
strWCond = DLookup("WCond", "FWSDRegTallyForms", "Title = '" & Forms![Division Tallies by Registrations]!Combo13 & "'")
DoCmd.Close
DoCmd.OpenForm strForm, , , "[FWSDRegTally].[EventClass] = '" & strWCond & "'"
End If

[pbaldy]

Glad you got it sorted out. I was stuck in a meeting.