Dlookup - msgbox

[Wayne-I-M]

Hi

9'15pm - too late for this (but got to sort it beore metting in the AM. Oh
dear.

Usng a Dlookup to check for existing records - no problems there. How would
I include the ID field in the message box if Dlookup finds a matching record.
So I can use this to DoCmd.OpenForm filtered (I can do this bit - it's just
the message I can't work out - too late ya see - mind numb)

Any help would get me a night's sleep



Private Sub SomeButton_Click()
If ((Dlookup goes here) Then
MsgBox "This person may have a record number XXXX", vbOKOnly, "New Booking
checker"
Else
MsgBox "No matching records", vbOKOnly, "New Booking checker"
End If
End Sub

The XXXX is the where i need the ID field from the table. The vbOkOnly will
not be there - just for example (thats the open form bit)

 

[George Nicholson]

Assign Dlookup results to a variable. Then you can evaluate that variable
and/or use the variable in a MsgBox prompt.

Dlookup will return Null if no matching records are found. I've wrapped
Dlookup in NZ because trying to assign Null to a string variable would cause
a type mismatch error.

Private Sub SomeButton_Click()
Dim strID as String

strID = Nz(Dlookup(Field,Domain,Criteria),"0")
If strID = "0" Then
MsgBox "No matching records", vbOKOnly, "New Booking checker"
Else
MsgBox "This person may have a record number " & strID, vbOKOnly, "New
Booking
checker."
End If
End Sub

 

[Wayne-I-M]

Oh yeah. Cheer for that.

I need another job. Maybe something with no screens

Many thanks


--
Wayne
Manchester, England.

Assign Dlookup results to a variable. Then you can evaluate that variable
and/or use the variable in a MsgBox prompt.

Dlookup will return Null if no matching records are found. I've wrapped
Dlookup in NZ because trying to assign Null to a string variable would cause
a type mismatch error.

Private Sub SomeButton_Click()
Dim strID as String

strID = Nz(Dlookup(Field,Domain,Criteria),"0")
If strID = "0" Then
MsgBox "No matching records", vbOKOnly, "New Booking checker"
Else
MsgBox "This person may have a record number " & strID, vbOKOnly, "New
Booking
checker."
End If
End Sub

 

[fredg]

Hi

9'15pm - too late for this (but got to sort it beore metting in the AM. Oh
dear.

Usng a Dlookup to check for existing records - no problems there. How would
I include the ID field in the message box if Dlookup finds a matching record.
So I can use this to DoCmd.OpenForm filtered (I can do this bit - it's just
the message I can't work out - too late ya see - mind numb)

Any help would get me a night's sleep

Private Sub SomeButton_Click()
If ((Dlookup goes here) Then
MsgBox "This person may have a record number XXXX", vbOKOnly, "New Booking
checker"
Else
MsgBox "No matching records", vbOKOnly, "New Booking checker"
End If
End Sub

The XXXX is the where i need the ID field from the table. The vbOkOnly will
not be there - just for example (thats the open form bit)

Your actual DLookUp would have been helpful.
CustName is the field to be found.
Change the Field and Table name and Criteria as needed.

The ID is a Number datatype?

Private Sub SomeButton_Click()
Dim strFound as String
On Error Resume Next

strFound = DLookUP("[ID] & [CustName]","TableName","[CustName] = '" &
Me.[CustName] & "'")

On Error Goto Err_Handler

If Len(strFound) >= 1 Then
MsgBox "This person may have a record number " & Val(strFound),
vbOKOnly, "New Booking checker"
Else
MsgBox "No matching records", vbOKOnly, "New Booking checker"
End If

Exit_Sub:
Exit Sub
Err_Handler:
MsgBox "Error #: & Err.Num
Resume Exit_Sub
End Sub

All this does in display a message. You have to then tell Access what
to do with the information.

 

[Wayne-I-M]

Many thanks for the input Fredg

I have it now. Just a late night mind block. Not as simple as it 1st seems
as this is basically prior to an append query to insert records imported from
sql DB imported from a website DB (loaded by on-line form).

We need to check if the person applying already has a record - it so insert
the booking details into the record. If not then create a new client record
and then append the booking details to the booking table and link it to the
new client.

Blah blah blah add on-line payment to banking audit trail, send to printer
details booking and confirmation, alocate rooms - Oh but before all this goes
on run another lookup to cheack availability. ha ha ha life goes on.

I am sure life was simpler with tipex and typewriters.

Many thanks again for taking the time to answer.

--
Wayne
Manchester, England.

Hi

9'15pm - too late for this (but got to sort it beore metting in the AM. Oh
dear.

Usng a Dlookup to check for existing records - no problems there. How would
I include the ID field in the message box if Dlookup finds a matching record.
So I can use this to DoCmd.OpenForm filtered (I can do this bit - it's just
the message I can't work out - too late ya see - mind numb)

Any help would get me a night's sleep

Private Sub SomeButton_Click()
If ((Dlookup goes here) Then
MsgBox "This person may have a record number XXXX", vbOKOnly, "New Booking
checker"
Else
MsgBox "No matching records", vbOKOnly, "New Booking checker"
End If
End Sub

The XXXX is the where i need the ID field from the table. The vbOkOnly will
not be there - just for example (thats the open form bit)

Your actual DLookUp would have been helpful.
CustName is the field to be found.
Change the Field and Table name and Criteria as needed.

The ID is a Number datatype?

Private Sub SomeButton_Click()
Dim strFound as String
On Error Resume Next

strFound = DLookUP("[ID] & [CustName]","TableName","[CustName] = '" &
Me.[CustName] & "'")

On Error Goto Err_Handler

If Len(strFound) >= 1 Then
MsgBox "This person may have a record number " & Val(strFound),
vbOKOnly, "New Booking checker"
Else
MsgBox "No matching records", vbOKOnly, "New Booking checker"
End If

Exit_Sub:
Exit Sub
Err_Handler:
MsgBox "Error #: & Err.Num
Resume Exit_Sub
End Sub

All this does in display a message. You have to then tell Access what
to do with the information.