Problem with a SELECT ... FROM ... WHERE statement (1 Viewer)

[anb001]

When running below code, it stops after the 'SELECT' statement.

Private Sub cmdUpdate_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim answer As String
Dim answer2 As String

If IsNull(Me.txtFullName.Value) Or Me.txtFullName = "" Then

    answer2 = MsgBox("Please choose someone to update.", vbOKOnly)
    Exit Sub
    
Else


Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM qryCrewStatus WHERE [CrewID] = '" & Me.txtCrewID & "'")

With rs
        .AddNew
        .Fields("Status") = Me.optStatus.Value
        .Fields("Arrival") = Me.txtArrival.Value
        .Fields("Departure") = Me.txtDeparture.Value
        .Fields("Shift") = Me.optShift.Value
        .Fields("MOBTeam") = Me.cboMOBTeam.Value
        .Fields("Lifeboat") = Me.cboLifeboat.Value
        .Fields("MusterCard") = Me.cboMusterCard.Value
        .Fields("OnDuty") = Me.cboOnDuty.Value
        .Fields("OffDuty") = Me.cboOffDuty.Value
        .Fields("Cabin") = Me.txtCabin.Value
        .Update
        MsgBox "Status updated!"
End With

Set db = Nothing
Set rs = Nothing


End If

End Sub

I have an idea that it might be something with the "txtCrewID". In that textbox a number is shown from a tabel/query (field is CreID), and that is an 'autonumber', formatted as Long Integer. I have formatted the textbox as a number as well, but still nothing happens when running the code. If both textbox and CrewID are formatted as text, it work fine.

Problem is probably after the "=" sign in the SELECT statement.

Can anyone assist?

Thanks.

 

[plog]

WHERE [CrewID] = '" & Me.txtCrewID & "'")

Your query is treating CrewID as text, not a number. Remove the quote marks around txtCrewID like so:

WHERE [CrewID] = " & Me.txtCrewID

 

[anb001]

Excellent.

Thanks.