Compare a query result to user input form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey all,
I'm trying to avoid double appending to a table.
I have macro that runs an update query to a table tbl_PBT. Before the macro
runs I wanted to check to see if the values on the userform match the values
on the appending table, if they do I want to exit the sub.
I need to write this check in the code before the docmd for the macro on the
userform_input.
I think what I need to do is an If And statement of somekind.. I'm having
trouble with the syntax.

private sub Append_Click()
If tbl_pbt!company=me!company and
if tbl_pbt!year=me!year and
if tbl_pbt!period=me!period then
exit sub
end if
docmd.runmacro "mcr_append"
end sub
 
actually I have the following code written for the comparision

If tbl_pbt!COMPANY = Me!COMPANY Then
If tbl_pbt![FISCAL YR] = Me!YEAR Then
If tbl_pbt![FISCAL QTR] = Me!PERIOD Then
Exit Sub
End If
End If
End If

but I'm getting an "object required error".
 
You can't refer to fields in tables like that in VBA code.

Try the following:

If IsNull(DLookup("COMPANY", "tbl_pbt", "COMPANY = " & _
Me!Company & " AND FISCAL_YR = " & _
Me!Year & " AND FISCAL_QTR = " & _
Me!Period)) = False Then
Exit Sub
End If

This assumes that COMPANY, FISCAL_YR and FISCAL_QRY are all numeric fields.
If they're text fields, you need to include quotes around the values:

If IsNull(DLookup("COMPANY", "tbl_pbt", "COMPANY = '" & _
Me!Company & "' AND FISCAL_YR = '" & _
Me!Year & "' AND FISCAL_QTR = '" & _
Me!Period & "'")) = False Then
Exit Sub
End If

where, exagerated for clarity, that's


If IsNull(DLookup("COMPANY", "tbl_pbt", "COMPANY = ' " & _
Me!Company & " ' AND FISCAL_YR = ' " & _
Me!Year & " ' AND FISCAL_QTR = ' " & _
Me!Period & " ' ")) = False Then
Exit Sub
End If

BTW, Year isn't a great choice for a control name: Year is a reserved word
in Access, and using it for your own purposes can lead to problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


TimT said:
actually I have the following code written for the comparision

If tbl_pbt!COMPANY = Me!COMPANY Then
If tbl_pbt![FISCAL YR] = Me!YEAR Then
If tbl_pbt![FISCAL QTR] = Me!PERIOD Then
Exit Sub
End If
End If
End If

but I'm getting an "object required error".

TimT said:
Hey all,
I'm trying to avoid double appending to a table.
I have macro that runs an update query to a table tbl_PBT. Before the macro
runs I wanted to check to see if the values on the userform match the values
on the appending table, if they do I want to exit the sub.
I need to write this check in the code before the docmd for the macro on the
userform_input.
I think what I need to do is an If And statement of somekind.. I'm having
trouble with the syntax.

private sub Append_Click()
If tbl_pbt!company=me!company and
if tbl_pbt!year=me!year and
if tbl_pbt!period=me!period then
exit sub
end if
docmd.runmacro "mcr_append"
end sub
Click to expand...
Click to expand...
 
I was working on a different variation of my original that would work better
if I can get this run.


If DLookup("'[ID]'", "qry_pbtbase").Value = Me.COMPANY(" ") & Me.NewYear &
(" ") & Me.NewPeriod Then
MsgBox "no"
Exit Sub
End If

qry_pbtbase.ID is a concatenation of the three fields, so if I can compare
the ID field to the concatenation of the userform fields this would work.
I just can't get the syntax right.

all fields are TEXT.


Douglas J Steele said:
You can't refer to fields in tables like that in VBA code.

Try the following:

If IsNull(DLookup("COMPANY", "tbl_pbt", "COMPANY = " & _
Me!Company & " AND FISCAL_YR = " & _
Me!Year & " AND FISCAL_QTR = " & _
Me!Period)) = False Then
Exit Sub
End If

This assumes that COMPANY, FISCAL_YR and FISCAL_QRY are all numeric fields.
If they're text fields, you need to include quotes around the values:

If IsNull(DLookup("COMPANY", "tbl_pbt", "COMPANY = '" & _
Me!Company & "' AND FISCAL_YR = '" & _
Me!Year & "' AND FISCAL_QTR = '" & _
Me!Period & "'")) = False Then
Exit Sub
End If

where, exagerated for clarity, that's


If IsNull(DLookup("COMPANY", "tbl_pbt", "COMPANY = ' " & _
Me!Company & " ' AND FISCAL_YR = ' " & _
Me!Year & " ' AND FISCAL_QTR = ' " & _
Me!Period & " ' ")) = False Then
Exit Sub
End If

BTW, Year isn't a great choice for a control name: Year is a reserved word
in Access, and using it for your own purposes can lead to problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


TimT said:
actually I have the following code written for the comparision

If tbl_pbt!COMPANY = Me!COMPANY Then
If tbl_pbt![FISCAL YR] = Me!YEAR Then
If tbl_pbt![FISCAL QTR] = Me!PERIOD Then
Exit Sub
End If
End If
End If

but I'm getting an "object required error".

TimT said:
Hey all,
I'm trying to avoid double appending to a table.
I have macro that runs an update query to a table tbl_PBT. Before the macro
runs I wanted to check to see if the values on the userform match the values
on the appending table, if they do I want to exit the sub.
I need to write this check in the code before the docmd for the macro on the
userform_input.
I think what I need to do is an If And statement of somekind.. I'm having
trouble with the syntax.

private sub Append_Click()
If tbl_pbt!company=me!company and
if tbl_pbt!year=me!year and
if tbl_pbt!period=me!period then
exit sub
end if
docmd.runmacro "mcr_append"
end sub
Click to expand...
Click to expand...
Click to expand...
 
Doug thanks!
When I use that code I get a "You canceled the previous operation" error.

Douglas J Steele said:
You can't refer to fields in tables like that in VBA code.

Try the following:

If IsNull(DLookup("COMPANY", "tbl_pbt", "COMPANY = " & _
Me!Company & " AND FISCAL_YR = " & _
Me!Year & " AND FISCAL_QTR = " & _
Me!Period)) = False Then
Exit Sub
End If

This assumes that COMPANY, FISCAL_YR and FISCAL_QRY are all numeric fields.
If they're text fields, you need to include quotes around the values:

If IsNull(DLookup("COMPANY", "tbl_pbt", "COMPANY = '" & _
Me!Company & "' AND FISCAL_YR = '" & _
Me!Year & "' AND FISCAL_QTR = '" & _
Me!Period & "'")) = False Then
Exit Sub
End If

where, exagerated for clarity, that's


If IsNull(DLookup("COMPANY", "tbl_pbt", "COMPANY = ' " & _
Me!Company & " ' AND FISCAL_YR = ' " & _
Me!Year & " ' AND FISCAL_QTR = ' " & _
Me!Period & " ' ")) = False Then
Exit Sub
End If

BTW, Year isn't a great choice for a control name: Year is a reserved word
in Access, and using it for your own purposes can lead to problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


TimT said:
actually I have the following code written for the comparision

If tbl_pbt!COMPANY = Me!COMPANY Then
If tbl_pbt![FISCAL YR] = Me!YEAR Then
If tbl_pbt![FISCAL QTR] = Me!PERIOD Then
Exit Sub
End If
End If
End If

but I'm getting an "object required error".

TimT said:
Hey all,
I'm trying to avoid double appending to a table.
I have macro that runs an update query to a table tbl_PBT. Before the macro
runs I wanted to check to see if the values on the userform match the values
on the appending table, if they do I want to exit the sub.
I need to write this check in the code before the docmd for the macro on the
userform_input.
I think what I need to do is an If And statement of somekind.. I'm having
trouble with the syntax.

private sub Append_Click()
If tbl_pbt!company=me!company and
if tbl_pbt!year=me!year and
if tbl_pbt!period=me!period then
exit sub
end if
docmd.runmacro "mcr_append"
end sub
Click to expand...
Click to expand...
Click to expand...
 
That cryptic error occurs if something is mistyped in the DLookup statement:
i.e. if a field or table name is misspelled.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


TimT said:
Doug thanks!
When I use that code I get a "You canceled the previous operation" error.

Douglas J Steele said:
You can't refer to fields in tables like that in VBA code.

Try the following:

If IsNull(DLookup("COMPANY", "tbl_pbt", "COMPANY = " & _
Me!Company & " AND FISCAL_YR = " & _
Me!Year & " AND FISCAL_QTR = " & _
Me!Period)) = False Then
Exit Sub
End If

This assumes that COMPANY, FISCAL_YR and FISCAL_QRY are all numeric fields.
If they're text fields, you need to include quotes around the values:

If IsNull(DLookup("COMPANY", "tbl_pbt", "COMPANY = '" & _
Me!Company & "' AND FISCAL_YR = '" & _
Me!Year & "' AND FISCAL_QTR = '" & _
Me!Period & "'")) = False Then
Exit Sub
End If

where, exagerated for clarity, that's


If IsNull(DLookup("COMPANY", "tbl_pbt", "COMPANY = ' " & _
Me!Company & " ' AND FISCAL_YR = ' " & _
Me!Year & " ' AND FISCAL_QTR = ' " & _
Me!Period & " ' ")) = False Then
Exit Sub
End If

BTW, Year isn't a great choice for a control name: Year is a reserved word
in Access, and using it for your own purposes can lead to problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


TimT said:
actually I have the following code written for the comparision

If tbl_pbt!COMPANY = Me!COMPANY Then
If tbl_pbt![FISCAL YR] = Me!YEAR Then
If tbl_pbt![FISCAL QTR] = Me!PERIOD Then
Exit Sub
End If
End If
End If

but I'm getting an "object required error".

:

Hey all,
I'm trying to avoid double appending to a table.
I have macro that runs an update query to a table tbl_PBT. Before
Click to expand...
the
macro
runs I wanted to check to see if the values on the userform match
Click to expand...
the
values
on the appending table, if they do I want to exit the sub.
I need to write this check in the code before the docmd for the
Click to expand...
macro on
the
userform_input.
I think what I need to do is an If And statement of somekind.. I'm having
trouble with the syntax.

private sub Append_Click()
If tbl_pbt!company=me!company and
if tbl_pbt!year=me!year and
if tbl_pbt!period=me!period then
exit sub
end if
docmd.runmacro "mcr_append"
end sub
Click to expand...
Click to expand...
Click to expand...
 
Take a look at
http://msdn.microsoft.com/library/en-us/vbaac11/html/acfctDLookup_HV05187182.asp
for details of how to use DLookup. You need to provide a WHERE clause. Your
DLookup statement will only return a single value back, so that it would be
coincidence if it was the one you're trying to check for.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


TimT said:
I was working on a different variation of my original that would work better
if I can get this run.


If DLookup("'[ID]'", "qry_pbtbase").Value = Me.COMPANY(" ") & Me.NewYear &
(" ") & Me.NewPeriod Then
MsgBox "no"
Exit Sub
End If

qry_pbtbase.ID is a concatenation of the three fields, so if I can compare
the ID field to the concatenation of the userform fields this would work.
I just can't get the syntax right.

all fields are TEXT.


Douglas J Steele said:
You can't refer to fields in tables like that in VBA code.

Try the following:

If IsNull(DLookup("COMPANY", "tbl_pbt", "COMPANY = " & _
Me!Company & " AND FISCAL_YR = " & _
Me!Year & " AND FISCAL_QTR = " & _
Me!Period)) = False Then
Exit Sub
End If

This assumes that COMPANY, FISCAL_YR and FISCAL_QRY are all numeric fields.
If they're text fields, you need to include quotes around the values:

If IsNull(DLookup("COMPANY", "tbl_pbt", "COMPANY = '" & _
Me!Company & "' AND FISCAL_YR = '" & _
Me!Year & "' AND FISCAL_QTR = '" & _
Me!Period & "'")) = False Then
Exit Sub
End If

where, exagerated for clarity, that's


If IsNull(DLookup("COMPANY", "tbl_pbt", "COMPANY = ' " & _
Me!Company & " ' AND FISCAL_YR = ' " & _
Me!Year & " ' AND FISCAL_QTR = ' " & _
Me!Period & " ' ")) = False Then
Exit Sub
End If

BTW, Year isn't a great choice for a control name: Year is a reserved word
in Access, and using it for your own purposes can lead to problems.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


TimT said:
actually I have the following code written for the comparision

If tbl_pbt!COMPANY = Me!COMPANY Then
If tbl_pbt![FISCAL YR] = Me!YEAR Then
If tbl_pbt![FISCAL QTR] = Me!PERIOD Then
Exit Sub
End If
End If
End If

but I'm getting an "object required error".

:

Hey all,
I'm trying to avoid double appending to a table.
I have macro that runs an update query to a table tbl_PBT. Before
Click to expand...
the
macro
runs I wanted to check to see if the values on the userform match
Click to expand...
the
values
on the appending table, if they do I want to exit the sub.
I need to write this check in the code before the docmd for the
Click to expand...
macro on
the
userform_input.
I think what I need to do is an If And statement of somekind.. I'm having
trouble with the syntax.

private sub Append_Click()
If tbl_pbt!company=me!company and
if tbl_pbt!year=me!year and
if tbl_pbt!period=me!period then
exit sub
end if
docmd.runmacro "mcr_append"
end sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top