Update table from a form

[jbiggs via AccessMonster.com]

I am trying to update a counter table from a form. Instead of updating, a
new entry is being created every time and the table end up looking like the
following:

Salesperson Date Leads
1 4/16/2007 0
1 4/16/2007 1
1 4/16/2007 0
1 4/16/2007 1

The code I'm using is below:

RepNum = DLookup("[Rep Number]", "Debt Advisors", "username = CurrentUser")
RepLeads = DLookup("[leads]", "fresh lead counter", [Salesperson] = RepNum)

If IsNull(RepLeads) Then
stDocName = "fresh lead counter"
stLinkCriteria = "[Salesperson] = " & RepNum & " And [todaysdate] =" & date
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

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

If whatday < date Then
stDocName = "fresh lead counter"
stLinkCriteria = "[Salesperson] = " & RepNum & " And [todaysdate] =" & date
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] = " & RepNum & " And [todaysdate] =" & date
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![fresh lead counter]![leads] = howmany + 1
Forms![fresh lead counter]![todaysdate] = date
Forms![fresh lead counter]![Salesperson] = RepNum
DoCmd.Close

 

[Alex Dybenko]

Hi,
suggest to use Update query instead of opening form and updating there:

so your code will look like
If IsNull(RepLeads) Then
docms.runsql "Update [fresh lead counter] Set [leads] = 0 Where
[Salesperson] = " & RepNum & " And [todaysdate] =date()"
end if


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

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

I am trying to update a counter table from a form. Instead of updating, a
new entry is being created every time and the table end up looking like
the
following:

Salesperson Date Leads
1 4/16/2007 0
1 4/16/2007 1
1 4/16/2007 0
1 4/16/2007 1

The code I'm using is below:

RepNum = DLookup("[Rep Number]", "Debt Advisors", "username =
CurrentUser")
RepLeads = DLookup("[leads]", "fresh lead counter", [Salesperson] =
RepNum)

If IsNull(RepLeads) Then
stDocName = "fresh lead counter"
stLinkCriteria = "[Salesperson] = " & RepNum & " And [todaysdate] =" &
date
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

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

If whatday < date Then
stDocName = "fresh lead counter"
stLinkCriteria = "[Salesperson] = " & RepNum & " And [todaysdate] =" &
date
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] = " & RepNum & " And [todaysdate] =" &
date
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![fresh lead counter]![leads] = howmany + 1
Forms![fresh lead counter]![todaysdate] = date
Forms![fresh lead counter]![Salesperson] = RepNum
DoCmd.Close