Dlookup and update not working correctly

[jbiggs via AccessMonster.com]

I am trying to do a Dlookup for the number of leads and date from a table.
If it is not today's date, then a new entry is created with the lead count =
0. The problem is that every user just keep incrementing the top entry if
the date is correct instead of finding the correct ID#. Here is the code:

RepNumber = DLookup("[Rep Number]", "Debt Advisors", "username = CurrentUser")

RepLeads = DLookup("[leads]", "fresh lead counter", [Salesperson] = RepNum)
Yesterday = date - 1

If RepLeads = Null Then

stDocName = "fresh lead counter"
stLinkCriteria = [Salesperson] = Me![Combo21]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![fresh lead counter]![leads] = 0
Forms![fresh lead counter]![todaysdate] = Yesterday
Forms![fresh lead counter]![Salesperson] = RepNum

DoCmd.Close

End If


whatday = DLookup("[todaysdate]", "fresh lead counter", [Salesperson] =
RepNum)

If whatday < date Then
stDocName = "fresh lead counter"
stLinkCriteria = [Salesperson] = Me![Combo21]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![fresh lead counter]![leads] = 0
Forms![fresh lead counter]![todaysdate] = date
Forms![fresh lead counter]![Salesperson] = RepNum
DoCmd.Close

End If



howmany = DLookup("[leads]", "fresh lead counter", [Salesperson] = RepNum)
maxleads = DLookup("[fresh leads per day]", "Debt Advisors",
"Username=CurrentUser")

If howmany < maxleads Then

DoCmd.OpenForm ("unasigned new leads2")
DoCmd.GoToRecord , , acFirst
Forms![unasigned new leads2]![Salesperson] = RepNum
DoCmd.Close

stDocName = "fresh lead counter"
stLinkCriteria = [Salesperson] = Me![Combo21]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![fresh lead counter]![leads] = howmany + 1
Forms![fresh lead counter]![todaysdate] = date

DoCmd.Close

End If

 

[jbiggs via AccessMonster.com]

I have made a few changes. The problem now is that the same record just
keeps being updated with a new RepNum and the count continues to increase.
How can I get a new record to be created and/or only updated the applicable
record?

 

[Rick Brandt]

I have made a few changes. The problem now is that the same record
just keeps being updated with a new RepNum and the count continues to
increase. How can I get a new record to be created and/or only
updated the applicable record?

Nothing is ever = Null You have to use...

Is Null (in SQL)
IsNull() (in VBA)

If IsNull(RepLeads) Then...

 

[jbiggs via AccessMonster.com]

Point taken, code modified. The problem actually exists in the last if
statement though, I am trying to get the stLinkCriteria to match both the
user and the date before the update but I am stuck.

 

[Rick Brandt]

Point taken, code modified. The problem actually exists in the last
if statement though, I am trying to get the stLinkCriteria to match
both the user and the date before the update but I am stuck.

I can;t seee that you are ever doing anything with stLinkCriteria besides...

stLinkCriteria = [Salesperson] = Me![Combo21]

Which is incorrect because it needs to be quoted like...

stLinkCriteria = "[Salesperson] = '" & Me![Combo21] & "'"
or
stLinkCriteria = "[Salesperson] = " & Me![Combo21]

depending on whether [Salespersion is a text field or a numeric one.



If you want to also match a date then you need something like...

stLinkCriteria = "[Salesperson] = '" & Me![Combo21] & "' AND SomeDateField =
#" & Format(SomeDateVariable,"yyyy-mm-dd") & "#"

 

[jbiggs via AccessMonster.com]

I made some changes and am not using the Combobox anymore. Code looks like:

stLinkCriteria = "[Salesperson] = RepNum" And "[todaysdate] = date"

Where RepNum is an int and Salesperson is a number field. todaysdate is
formatted like mm/dd/yyyy.

Point taken, code modified. The problem actually exists in the last
if statement though, I am trying to get the stLinkCriteria to match
both the user and the date before the update but I am stuck.

I can;t seee that you are ever doing anything with stLinkCriteria besides...

stLinkCriteria = [Salesperson] = Me![Combo21]

Which is incorrect because it needs to be quoted like...

stLinkCriteria = "[Salesperson] = '" & Me![Combo21] & "'"
or
stLinkCriteria = "[Salesperson] = " & Me![Combo21]

depending on whether [Salespersion is a text field or a numeric one.

If you want to also match a date then you need something like...

stLinkCriteria = "[Salesperson] = '" & Me![Combo21] & "' AND SomeDateField =
#" & Format(SomeDateVariable,"yyyy-mm-dd") & "#"

 

[Rick Brandt]

I made some changes and am not using the Combobox anymore. Code
looks like:

stLinkCriteria = "[Salesperson] = RepNum" And "[todaysdate] = date"

Where RepNum is an int and Salesperson is a number field. todaysdate
is formatted like mm/dd/yyyy.

The way you've written that it ois going to assume tha RepNum is a field in
the Report's query. You need to put the variable outside the quotes. Also,
the word "And" needs to be inside the quotes.

stLinkCriteria = "[Salesperson] = " & RepNum & " And [todaysdate] = date"

Now... what is "date" above and what is "[todaysdate]"? If you mean the VBA
function Date() then that will always be equal to "todays date". If
"[todaysdate]" contains what its name implies then that would seem to be an
unnecessary thing to have in there.

If you are using "date" for anything else then change that. It is a
reserved word and will be confused with the VBA function.

 

[jbiggs via AccessMonster.com]

Rick,

It seems to be working, thanks so much for all your help. Do you have any
resources you would recommend for figuring out this kind of syntax?

Thanks again,

Justin

I made some changes and am not using the Combobox anymore. Code
looks like:

[quoted text clipped - 3 lines]

Where RepNum is an int and Salesperson is a number field. todaysdate
is formatted like mm/dd/yyyy.

The way you've written that it ois going to assume tha RepNum is a field in
the Report's query. You need to put the variable outside the quotes. Also,
the word "And" needs to be inside the quotes.

stLinkCriteria = "[Salesperson] = " & RepNum & " And [todaysdate] = date"

Now... what is "date" above and what is "[todaysdate]"? If you mean the VBA
function Date() then that will always be equal to "todays date". If
"[todaysdate]" contains what its name implies then that would seem to be an
unnecessary thing to have in there.

If you are using "date" for anything else then change that. It is a
reserved word and will be confused with the VBA function.

 

[Rick Brandt]

Rick,

It seems to be working, thanks so much for all your help. Do you
have any resources you would recommend for figuring out this kind of
syntax?

Thanks again,

Not really. They key to to look at how the where clause your building will look
when it is evaluated out to a string. A typical one similar to yours made
completely out of literals would look like...

[Salesperson] = 123456 And [todaysdate] = #2007-04-11#

You just have to use your variables and/or form references intermixed with
literals so that the result has the same format as if you used all literals. A
good help is to use...

stLinkCriteria = "[Salesperson] = " & RepNum & " And [todaysdate] = date"
Debug.Print stLinkCriteria

That second line will evaluate the entire expression into a string and print it
to the immediate pane of the VBA window. Then you can examine those results to
see if it looks to be properly constructed.

 

[jbiggs via AccessMonster.com]

Thanks again Rick, that debug command should definitely come in handy. I do
have another problem now. The code is creating new entries in the table
instead of updating the existing record. So taking 4 leads with Rep 1 would
make the table look like:

Salesperson todaysdate Leads
1 4/12/2007 0
1 4/12/2007 1
1 4/12/2007 0
1 4/12/2007 1

Am I doing something incorrectly with those form procedures?

Rick,

[quoted text clipped - 3 lines]

Thanks again,

Not really. They key to to look at how the where clause your building will look
when it is evaluated out to a string. A typical one similar to yours made
completely out of literals would look like...

[Salesperson] = 123456 And [todaysdate] = #2007-04-11#

You just have to use your variables and/or form references intermixed with
literals so that the result has the same format as if you used all literals. A
good help is to use...

stLinkCriteria = "[Salesperson] = " & RepNum & " And [todaysdate] = date"
Debug.Print stLinkCriteria

That second line will evaluate the entire expression into a string and print it
to the immediate pane of the VBA window. Then you can examine those results to
see if it looks to be properly constructed.