Opening a Form Using a Combobox and a Button

[Playfulmutt]

Hello! I'm having some trouble opening a group of forms using a combobox. I think I'm really close, but I'm having some errors that prevent me from doing so.

I have a Combobox called "Letter Type" that contains the names of all of the other forms that exist. I want to be able to select a form from the combobox and click on the button "Compose." This should then open that form.

I had some trouble trying to get the Combobox value to work properly so I decided I would just create a hard workaround by using If/Then statements.

This is what I have in the On_Click event of my button.

Private Sub Compose_Click()
If Me.Letter_Type = "Refund Request" Then
strForm = "Refund_Request"
ElseIf Me.Letter_Type = "Request for Records" Then
strForm = "Request_for_Records"
End If
DoCmd.OpenForm strForm, , , Me.[Reference_#] = Forms![Reference_#]
End Sub

The catch is that I want the form to only open with one record and I want that record to be the same record the user was viewing on the previous form. I thought I would tie them together using the "Reference #" since it is unique.

When I click on Compose I get a "type mismatch" on the Reference #, though I'm not sure why.

Any help you can provide would be useful.

 

[JHB]

The catch is that I want the form to only open with one record and I want that record to be the same record the user was viewing on the previous form. I thought I would tie them together using the "Reference #" since it is unique.

When I click on Compose I get a "type mismatch" on the Reference #, though I'm not sure why.

What is the previous form, the form in which you've the code running or?
About the combobox problem is the correct form name with or without underscore?
If the form name is exact as shown in the combobox you could use:

DoCmd.OpenForm Me.Letter_Type, ...

The Where condition in the need to be a string expression, like

DoCmd.[B]OpenForm[/B] "Employees", , ,"LastName = 'King'"

It is a bad idea to use special character in field name, like "#%&" etc.

 

[Playfulmutt]

I don't doubt that using special characters is a bad idea in programming, but unfortunately I have to work with what I have.

The original form that this button is on is actually several forms--each end user has their own data entry form whose control source is a query for their own caseload.

So it might be named something like "Data Entry Form - John Doe"

You are right--since the names of my forms are the exact same in the ComboBox I can just use "DoCmd.OpenForm Me.Letter_Type, ..." But I still want to open only one record tied to the Reference #.

 

[Playfulmutt]

I'm really close. I can almost taste it.

I took your advice, JHB (thanks for the second opinion on that) and used the Letter Type field (which I changed to Last Correspondence because the VBA doesn't like the word "type.").

This is what I have now.

Private Sub Compose_Click()
DoCmd.OpenForm Me.Last_Correspondence, , , "Reference__= " & Me.Reference__
End Sub

It will open the form name of whatever is in the Last Correspondence field, but it asks me for the Reference # for the record I want. If I leave it blank it will create a new record.

I don't want this at all. I want whatever the current record is on the data entry form. Does anyone know a quick workaround this pop up box?

 

[Playfulmutt]

This is an odd twist of events. The person who asked the question found the answer, but I wouldn't have gotten around to it if JHB didn't chime in about the # character. It wasn't my first choice, so I opted not to change it. After I did, however, everything fell into place. Let this be a lesson to never use special characters when you're designing an Access database.

So I changed the field name to "Reference," then changed the coding accordingly.

Private Sub Compose_Click()
DoCmd.OpenForm Me.Last_Correspondence, , , "Reference= " & Me.Reference
End Sub

As long as you have the Reference field on all of your forms it won't prompt you for the value and it will provide you with a 1/1 record for the Reference number that you were currently browsing from the data entry form.

For whatever reason the # symbol REALLY messes with the coding on this. It won't let you leave a hanging # in the code, so it formats it as an underscore (hence __). This means it isn't exactly the same as the field name on your other forms, because Reference_# =/= Reference__.

Thanks again, JHB, for putting that bug in my ear. I'll just have to change all of the occurences of the Reference number in the database. It's a small price to pay to ensure the end user can actually use it as intended.

 

[JHB]

Good you got it solved.
Maybe putting the control/field name between [] could have helped, but using special character in field name, will always follow you like a nightmare!