Search for existing record or add new

[BT Barnett]

I am constructing an Access database using VBA for the first time. I wish to search for an "MMSI" number (unique ship number used as Primary Key) to see if it exists. If it does, open the record, if not ask to add it as a new record.


I've attempted to put together some VBA code I discovered from a similar database. My code appears to be working in so much as if the MMSI doesn't exist it will allow me to add it to the table. However, if the MMSI number exists I simply get an error code. I have tried everything that I know (which is very limited) but can't find a solution. Can you please help.
The error I get is in this line: DoCmd.OpenForm "frmShipData", , , "MMSI=" & ID

This is the subroutine in question:


Private Sub ShipSearch_AfterUpdate()

Dim ID As Long

ID = Nz(DLookup("MMSI", "tblShip", "MMSI=""" & ShipSearch & """"), 0)
If ID = 0 Then
' doesn't exist
If MsgBox("Ship is not in database. Add as new ship?", vbYesNoCancel + vbQuestion, _
"Add New?") = vbYes Then
' add as new ship
DoCmd.OpenForm "frmShipData", , , , acFormAdd
Forms!frmShipData!MMSI = ShipSearch
ShipSearch = ""
Else
' do nothing
Exit Sub
End If

Else
' Ship exists'
DoCmd.OpenForm "frmShipData", , , "MMSI=" & ID
ShipSearch = ""
End If


End Sub

[June7]

MMSI is a string so why is ID declared as integer type? Don't really need this variable.
The ShipSearch = "" line could be outside the If Then block.
The first Else is not necessary.

    With Me
    If Nz(DLookup("MMSI", "tblShip", "MMSI='" & .ShipSearch & "'"), "") = "" Then
        ' doesn't exist
        If MsgBox("Ship is not in database. Add as new ship?", vbYesNo + vbQuestion, "Add New?") = vbYes Then
            ' add as new ship
            DoCmd.OpenForm "frmShipData", , , , acFormAdd
            Forms!frmShipData!MMSI = .ShipSearch
        End If
    Else
        ' Ship exists'
        DoCmd.OpenForm "frmShipData", , , "MMSI='" & .ShipSearch & "'"
    End If
    .ShipSearch = ""
    End With

Might want to use ValidationRule and ValidationText properties of textbox to make sure user enters a valid value. Or use a combobox for selecting MMSI value and its NotInList event.

Advise not to use exact same field name in multiple tables. For the MMSI foreign key, consider MMSI_fk.

Note for future, post code between CODE tags to retain indentation and readability. Click # icon on post edit toolbar.

[CJ_London]

And what is the error description?

[Welshgasman]

And what is the error description?

Type Mismatch CJ.

BT Barnett, when you ask for help, give us what errors you get. Do not make us go looking for them or guessing.

Change your code to

    Else
        ' Ship exists'
        DoCmd.OpenForm "frmShipData", , , "MMSI='" & ID & "'"
        ShipSearch = ""
    End If

Learn the difference between text, numbers and dates and how they are referenced.

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?
Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.
Added benefit is, if you still cannot see the error, you can copy and paste back here the output from the debug.print for someone else to spot it.
Example:
tt="Eg'g"
? dlookup("FoodID","tblFoods","FoodName = """ & tt & """")

[BT Barnett]

Thank you for solving my query. I am extremely grateful.
Brian Barnett