Search table from form

[MyMel]

Hi,

I have a customer request entry form and after information is taken a button
is pushed to send an email to management for approval. Each customer request
form is assigned a tracking number. When the email is sent it keeps a
log(MailReview_Log) of the email sent. I am trying to create a validation
point to check the table(MailReview_Log) for the tracking number to see if an
email has already been sent with that tracking number on my form before
sending. I am having a dilly of a time putting this together. I get a Error
2465 "Can't find the field MailReview_Log", which I understand because it is
a table name. Any and all assistance IS greatly appreciated.

If IsNull(Me.[Control_Number]) Then
MsgBox "Please complete request."
ElseIf ((Me.[Control_Number]) = Me!MailReview_Log.[Control_Number]) Then
MsgBox "Request sent for review on " '& " "
Else
cmdMailTicket_Click
End if

 

[Jack Leach]

If IsNull(Me.[Control_Number]) Then

MsgBox "Please complete request."
ElseIf ((Me.[Control_Number]) = Me!MailReview_Log.[Control_Number]) Then
MsgBox "Request sent for review on " '& " "
Else
cmdMailTicket_Click
End if

Me!MailReview_Log <- this line is telling access to find the field
(MailReview_Log) on the current form (Me). What you need to find it in a
table is DLookup. If DLookup returns Null, the value doesn't exist...

If IsNull(Me.Control_Number]) Then
MsgBox "Please Complete Request"
Else
If Not IsNull(DLookup("FieldName", "Tablename", "Fieldname = " &
Me.ControlNumber)) Then
cmdMailTicket_Click
Else
MsgBox "Request Already Sent"
End If
End If


See vba help for further details on DLookup

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Hi,

I have a customer request entry form and after information is taken a button
is pushed to send an email to management for approval. Each customer request
form is assigned a tracking number. When the email is sent it keeps a
log(MailReview_Log) of the email sent. I am trying to create a validation
point to check the table(MailReview_Log) for the tracking number to see if an
email has already been sent with that tracking number on my form before
sending. I am having a dilly of a time putting this together. I get a Error
2465 "Can't find the field MailReview_Log", which I understand because it is
a table name. Any and all assistance IS greatly appreciated.

If IsNull(Me.[Control_Number]) Then
MsgBox "Please complete request."
ElseIf ((Me.[Control_Number]) = Me!MailReview_Log.[Control_Number]) Then
MsgBox "Request sent for review on " '& " "
Else
cmdMailTicket_Click
End if

 

[MyMel]

Thank you, Jack that worked just fine.

If IsNull(Me.[Control_Number]) Then
MsgBox "Please complete request."
ElseIf ((Me.[Control_Number]) = Me!MailReview_Log.[Control_Number]) Then
MsgBox "Request sent for review on " '& " "
Else
cmdMailTicket_Click
End if

Me!MailReview_Log <- this line is telling access to find the field
(MailReview_Log) on the current form (Me). What you need to find it in a
table is DLookup. If DLookup returns Null, the value doesn't exist...

If IsNull(Me.Control_Number]) Then
MsgBox "Please Complete Request"
Else
If Not IsNull(DLookup("FieldName", "Tablename", "Fieldname = " &
Me.ControlNumber)) Then
cmdMailTicket_Click
Else
MsgBox "Request Already Sent"
End If
End If


See vba help for further details on DLookup

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Hi,

I have a customer request entry form and after information is taken a button
is pushed to send an email to management for approval. Each customer request
form is assigned a tracking number. When the email is sent it keeps a
log(MailReview_Log) of the email sent. I am trying to create a validation
point to check the table(MailReview_Log) for the tracking number to see if an
email has already been sent with that tracking number on my form before
sending. I am having a dilly of a time putting this together. I get a Error
2465 "Can't find the field MailReview_Log", which I understand because it is
a table name. Any and all assistance IS greatly appreciated.

If IsNull(Me.[Control_Number]) Then
MsgBox "Please complete request."
ElseIf ((Me.[Control_Number]) = Me!MailReview_Log.[Control_Number]) Then
MsgBox "Request sent for review on " '& " "
Else
cmdMailTicket_Click
End if