Increment Count In Another Table

[Parts Manager]

tblCounter: nxtCountName, nxtCountInteger
(invCount) is nxtCountName for this counter in that table

tblInvoice: invId, etc...
I would like to assign the resulting nxtCountInteger from invCount into this
field on the form called 'invId'.


I have figured out using Dlookup to retrieve the value stored in tblCounter
using the name of the counter (invCount) and then getting the integer valued
stored for that field, but I am not sure how I update the process.

What section of the form should I put code to update the 'nxtCountInteger'
field in the tblCounter table? I want to put it in the right place so that
it only gets the counter and updates the form (table) one time upon entry,
and no other time.

What is the command(s) to update it by one? I want to create the invId by
getting the next integer and save the record; but afterwards I want to tell
the master tblCounter that this counter needs to be updated by one for the
next usage.

Can anyone offer suggestions on the above?

BTW, this is my first major form I am creating.

Tim

 

[Steve Schapel]

Tim,

If I understand you correctly, then I think you could put code like the
following on the Before Update event of the form...

If Me.NewRecord Then
Me.invID = DLookup("[nxtCountInteger]","tblCounter")
CurrentDb.Execute "UPDATE tblCounter SET nxtCountInteger =
nxtCountInteger + 1"
End If

However, I couldn't quite understand how invCount comes into the
picture, so I may have oversimplified.

 

[Parts Manager]

Tim,

If I understand you correctly, then I think you could put code like the
following on the Before Update event of the form...

If Me.NewRecord Then
Me.invID = DLookup("[nxtCountInteger]","tblCounter")
CurrentDb.Execute "UPDATE tblCounter SET nxtCountInteger =
nxtCountInteger + 1"
End If

However, I couldn't quite understand how invCount comes into the picture,
so I may have oversimplified.

Steve, thanks for the reply. I will review the code you posted above and
try it out on Monday when I return from the weekend.

I think I may have forgot to preface that 'invCount' is a field in a table
'tblCounter'.

tblCounter: nxtCountName, nxtCountInteger
(invCount) is nxtCountName for this counter in that table

tblCounter: nxtCountName, nxtCountInteger

So I have;
ordCount, 1
invCount, 1
ordlineCount, 1
invlineCount, 1

The above is what I imagine the table will look like as I put in data. Then
using your code above, hope to increase these numbers as I use them. Per my
first form of Invoicing in my example, the field to lookup in tblCounter is
'invCount' and right now being fresh will be 1. I want to get the integer
'1' for the first invoice and then update that number to a 2 in THAT table
for next usage. Then if I created a 2nd invoice, I could use your code in
my form so that it would now find the field 'invCount' and retrieve a value
of '2' for the next invoice number id.

Maybe that will explain a bit better.

By reading your code above, it looks like what I need.

PS: I wonder if the above code could be altered so that if a field was not
found, it could add the field as well as put a starting integer of 1 (one)
in that field? Might save some data entry later as I create other forms
that might have a count field in that table. Mainly a form I plan to make
later that will look up a primary build table and a secondary build table;
from these codeId's, I will have a count for incrementing part assignments.
I can manually add each combination of primary/secondary build Ids and give
it a starting count of 1 (one), however it would be nice if I could code it
directly.

Tim

 

[Steve Schapel]

Tim,

Thanks for the further information.

Ok, now I understand the purpose of the nxtCountName field, we will just
need to adjust the code accordingly...

If Me.NewRecord Then
Me.invID =
DLookup("[nxtCountInteger]","tblCounter","[nxtCountName]='invCount'")
CurrentDb.Execute "UPDATE tblCounter SET nxtCountInteger =
nxtCountInteger + 1 WHERE [nxtCountName]='invCount'"
End If

(allow for word wrap in the newsreader)

It is certainly possible to write code to add a record to the tblCounter
table, but in my opinion this would be overkill, adding complexity and
overhead for no real benefit.

--
Steve Schapel, Microsoft Access MVP

Tim,

If I understand you correctly, then I think you could put code like the
following on the Before Update event of the form...

If Me.NewRecord Then
Me.invID = DLookup("[nxtCountInteger]","tblCounter")
CurrentDb.Execute "UPDATE tblCounter SET nxtCountInteger =
nxtCountInteger + 1"
End If

However, I couldn't quite understand how invCount comes into the picture,
so I may have oversimplified.

Steve, thanks for the reply. I will review the code you posted above and
try it out on Monday when I return from the weekend.

I think I may have forgot to preface that 'invCount' is a field in a table
'tblCounter'.

tblCounter: nxtCountName, nxtCountInteger
(invCount) is nxtCountName for this counter in that table

tblCounter: nxtCountName, nxtCountInteger

So I have;
ordCount, 1
invCount, 1
ordlineCount, 1
invlineCount, 1

The above is what I imagine the table will look like as I put in data. Then
using your code above, hope to increase these numbers as I use them. Per my
first form of Invoicing in my example, the field to lookup in tblCounter is
'invCount' and right now being fresh will be 1. I want to get the integer
'1' for the first invoice and then update that number to a 2 in THAT table
for next usage. Then if I created a 2nd invoice, I could use your code in
my form so that it would now find the field 'invCount' and retrieve a value
of '2' for the next invoice number id.

Maybe that will explain a bit better.

By reading your code above, it looks like what I need.

PS: I wonder if the above code could be altered so that if a field was not
found, it could add the field as well as put a starting integer of 1 (one)
in that field? Might save some data entry later as I create other forms
that might have a count field in that table. Mainly a form I plan to make
later that will look up a primary build table and a secondary build table;
from these codeId's, I will have a count for incrementing part assignments.
I can manually add each combination of primary/secondary build Ids and give
it a starting count of 1 (one), however it would be nice if I could code it
directly.

Tim