CODEWITHSUNDEEP
databasevbams-access
Bob P
Bob P

Reputation: 237

Trying to use lookup to find a field in VBA

I think I am close I just don't know the exact syntax.

I am trying to use VBA to select a record based on the value of the SCID (Primary Key) from my table Stock Conversion.

If LookUp("Source PC", "Stock Conversion") Is Null Then
sc1.AddNew
sc1.Fields("[Source PC]").Value = Me.cmbSource.Value
sc1.Update
End If

This is the code I have now, as you can see I only want the field to be populated if the value is Null. It needs to find the Source PC where SCID = Record.

Thanks in advance, Bob P

Upvotes: 2

Views: 110

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

This is where you should use a recordset.

Dim rs As DAO.Recordset
Dim sSQL As String

'SQL to select a record from a table MyTable based on a field or control
'called ID in the current form
sSQL="Select Id,Field1 from MyTable Where Id=" & Me.ID

'Open the recordset
Set rs = CurrentDB.OpenRecordset(sSQL)
'Test for End Of File to see if the recordset is empty ...
If rs.Eof Then
   'add a new record
   rs.AddNew 
   rs!Field1 = "Something"
   rs.Update
Else
   '... otherwise, do what you want with the record,
   'let us say edit.
   rs.edit
   rs!Field1 = "Something"
   rs.Update
End If

Upvotes: 3

Related Questions