Compare a query result to user input form

[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

 

[Guest]

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".

 

[Douglas J Steele]

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!)

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 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

 

[Guest]

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.

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!)

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 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

 

[Guest]

Doug thanks!
When I use that code I get a "You canceled the previous operation" 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!)

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 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

 

[Douglas J Steele]

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!)

Doug thanks!
When I use that code I get a "You canceled the previous operation" 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!)

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

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

 

[Douglas J Steele]

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!)

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.

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!)

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

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