2 tbles, 1 qry, 1 frm, 3 fields, New Rec create, Msg box, Input box, for 3 scenarios

[June7]

I don't know your data structure so I used a field name commonly found in tables as a unique identifier. Access Help has info on domain aggregate functions (DLookup, DSum, DAvg, etc).




InputBox has only one button - OK.

MsgBox can have various combinations of boxes.


You will get more info from VBA Help on InputBox and MsgBox syntax.

[Rramjet]

Ok -

Now I am getting there. I have an "Add client" button with OnClick...

DoCmd.GoToRecord , , acNewRec
Me!ID.Locked = False
If IsNull(DLookup("ID", "CLIENT", "ID='" & InputBox("Enter the ID to search") & "'")) Then
MsgBox "There is no client associated with the ID you entered. You cannot enter OM_1 data for this client until there is a valid entry in the CLIENT table.", vbOKOnly
Else
MsgBox "A client with that ID exists. You may create a new record for this client.", vbOKOnly
End If

Which is perfect as far as it goes... However, I need another statement that runs something like (in English)

If the ID is found, enter that ID into the frmOM_1 ID field and exit,
Else if
the ID is not found, go to the first record.

Now for the latter half I can use:
If IsNull([ID]) then
DoCmd.GoToRecord , , acFirst

...because unless the ID field is filled during the procedure, it will (naturally) be empty. So, I just need a way to set the field if the ID is found (with the ID that has been found).

[Rramjet]

Closer...

DoCmd.GoToRecord , , acNewRec
Me!ID.Locked = False
If IsNull(DLookup("ID", "CLIENT", "ID='" & InputBox("Enter the ID to search") & "'")) Then
MsgBox "There is no client associated with the ID you entered. You cannot enter OM_1 data for this client until there is a valid entry in the CLIENT table.", vbOKOnly
DoCmd.GoToRecord , , acFirst
Else
MsgBox "A client with that ID exists. You may create a new record for this client.", vbOKOnly

(Now All I need here is a command to set the frmOM_1 ID = the tblCLIENT ID whenever it is found)

End If

[June7]

Could use and populate a variable with the DLookup.

Dim intID As Integer
intID = Nz(DLookup(...),0)
If intID = 0 Then
'ID not found, do this with intID
Else
'ID found, do this with intID
End If

[Rramjet]

Ummm ... thanks June7, but I am not quite sure I understand the application there...

I need to capture the ID that is typed into the input box - and if the ID is found, then use that ID to set the ID field on the form to that ID value.

I need to:

Dim strID As String

'Then I need to capture the Input box ID that was typed in

strID = '....here I am stuck and need help...

'then

Me.ID = strID


I may be being obtuse, but I am just not sure how the code you just posted helps me toward that end...

[June7]

Sorry, don't understand your confusion. Use the same DLookup expression already constructed. Populate a variable with result of DLookup. Use the variable for further processing.

Do you understand use of variables in code?

Is the ID a number or text field?

[Rramjet]

It's a text field ..and sorry but no, I don't understand the use of variables in code (as I say - I really am a COMPLETE novice to Access and VBa - a couple of weeks ago I opened Access for the first time in my life) So, I have ...

Dim strID As String

DoCmd.GoToRecord , , acNewRec
Me!ID.Locked = False
If IsNull(DLookup("ID", "CLIENT", "ID='" & InputBox("Enter the ID to search") & "'")) Then
MsgBox "There is no client associated with the ID you entered. You cannot enter OM_1 data for this client until there is a valid entry in the CLIENT table.", vbOKOnly
DoCmd.GoToRecord , , acFirst
Me!ID.Locked = False
Else
MsgBox "A client with that ID exists. You may create a new record for this client.", vbOKOnly

Me.ID = strID

End If

...So how DO I populate the strID with the result of DLookup within the structure above?

[June7]

Dim strID As String

DoCmd.GoToRecord , , acNewRec
Me!ID.Locked = False

strID = Nz(DLookup(...),"")

If strID = "" Then
...
Else
...
End If


http://office.microsoft.com/en-us/ac...001214213.aspx

http://www.access-vba.net/introducti...ccess-vba.html

http://www.lynda.com/Access-training...FQThQgod7lMAyg

[Rramjet]

YES! Thank you June7! The whole thing now works perfectly and precisely as I wanted. I now have precisely what I needed. You have (for what it is worth) earned my undying gratitude.

Not sure how this forum works, but If I could tick a "solved" button - I would!
Rramjet.

[June7]

Thread Tools dropdown above first post.

Done.