Open form to record # in ComboBox

[crimedog]

Okay - I am close.


I have the Combo18 defaulted to 0
when I click the button - if the Combo18 is 0, it opens Form 00_360_Request_360 to a new record, this works fine
I want : If Combo18 is not 0 (Combo18 is populated from the Primary Key "TicketID" (AutoNumber) on the table) or >0 I want it to open to the record number that is in Combo18
Code so far:

Private Sub Command0_Click()
Dim VarID As Integer
VarID = Me.Combo18
If VarID = 0 Then
    DoCmd.OpenForm "00_360_Request_360", , , , , acHidden
    DoCmd.GoToRecord acDataForm, "00_360_Request_360", acNewRec
    DoCmd.OpenForm "00_360_Request_360", , , , , acDialog
ElseIf VarID > 0 Then
    DoCmd.OpenForm "00_360_Request_360", , , , , acHidden
    DoCmd.GoToRecord acDataForm, "00_360_Request_360", "TicketID=" & VarID
    DoCmd.OpenForm "00_360_Request_360", , , , , acDialog
End If

Error Msg: Type Mismatch on line:

DoCmd.GoToRecord acDataForm, "00_360_Request_360", "TicketID=" & VarID

[ItsMe]

PK's and FK's are typically of type Long Integer in Access because of the AutoNumber type
Dim VarID As Long

[crimedog]

made the change

Dim VarID As Long

Still getting type mismatch on the same line

DoCmd.GoToRecord acDataForm, "00_360_Request_360", "TicketID=" & VarID

I checked the Table: TicketID is the right column
It is in the Form as a Txt Box TicketID

[ItsMe]

I guess that kinda makes sense because the initialization of the variable would throw an exception if the combo value was larger than 32,200(or thereabout). Your code would not have gotten to the Goto method.

Not sure what this means
I checked the Table: TicketID is the right column
It is in the Form as a Txt Box TicketID


If the textbox is bound to the table, you want to verify the data type in the field in the table. If everything stated within this thread is fact, that leaves data type text at the table.

Perhaps you are confusing an error you received earlier when your variable was type variant. If this is the case, the issue most likely lies in the combo where the combo is multicolumn and you are initializing your variable with the wrong column form the combo.

[crimedog]

the combo box is unbound - The data row source is query based on the Table field "TicketID"
Data Type in table is AutoNumber

I set the ComboBox format : General Number when I started getting the error

Column count = 1 Bound Column =1

[ItsMe]

What is the data type of the field named, "TicketID"? While in design view of the table object, check the properties of the field to verify its data type.

[crimedog]

Auto Number : Long Integer

[ItsMe]

Try this... Go to your table in Datasheet View and locate a random record. Make a note of the value for field TicketID (maybe something like 500). Now test some VBA code using a literal value. See if you can get your Command Button to open form "00_360_Request_360" and display record 500 or another number of your choice.

Use the following code in your Command Buttons click event. Notice I commented out all of your original code and added one line for testing purposes.

DoCmd.OpenForm "00_360_Request_360", , , "TicketID=500"
'
'
'
'Dim VarID As Integer
'VarID = Me.Combo18
'If VarID = 0 Then
'    DoCmd.OpenForm "00_360_Request_360", , , , , acHidden
'    DoCmd.GoToRecord acDataForm, "00_360_Request_360", acNewRec
'    DoCmd.OpenForm "00_360_Request_360", , , , , acDialog
'ElseIf VarID > 0 Then
'    DoCmd.OpenForm "00_360_Request_360", , , , , acHidden
'    DoCmd.GoToRecord acDataForm, "00_360_Request_360", "TicketID=" & VarID
'    DoCmd.OpenForm "00_360_Request_360", , , , , acDialog

After you get that to work, continue trouble shooting your code with the literal value of 500 or another number of your choice. The very last step/implementation should be implementing the combo to dynamically set the value of a variable. For now, use the literal value 500 (or...) until you get the functionality you are after.

[crimedog]

good thought
I tried that and it opens at the first record (not the record number we chose)

[ItsMe]

The code I provided will open the form with a filtered recordsetset. It will display one record. The record will represent the value in the Where Criteria "TicketID=500"

If there is not a match, you will see a blank form without any controls displayed.

[crimedog]

Finally got it to work - I discarded the GotoRecord and used Find Record: works great now

Dim VarID As Long
VarID = Me.Combo18
If VarID = 0 Then
    DoCmd.OpenForm "00_360_Request_360", , , , , acHidden
    DoCmd.GoToRecord acDataForm, "00_360_Request_360", acNewRec
    DoCmd.OpenForm "00_360_Request_360", , , , , acDialog
ElseIf VarID > 0 Then
    DoCmd.OpenForm "00_360_Request_360", , , , , acHidden
    DoCmd.FindRecord VarID, , True, , True
    DoCmd.OpenForm "00_360_Request_360", , , , , acDialog
    
End If
DoCmd.Close acForm, Me.Name