Semi-Nube Simple DLookup Issue - Help?

[bimcompu]

Hi guys - I have a simple problem that has been nagging me for days on end. Here goes, I hope I can explain it:



I have a simple table called LastBatch - it has 2 fields and 3 records - field names are Type and BatchNum

Type	LastBatch
NH	400
LCD	345
FS	012

Type is a Two or three character name and the BatchNum is a 3 character number

All I am trying to do is lookup the type and find the value of BatchNum, add 1 to it and write it back to BatchNum for the next use

Example: FS is the type and BatchNum value is 012

when I click a radio button in the form I want it to update the table so that the value of NH becomes 13

I have gotten it to count without a problem but it always writes back to the first record in the table even though FS is record three

Here is my code:

Private Sub Option8_Click()
Dim NewBatch
Dim LastBatch1
NewBatch = DLookup("[BatchNum]", "LastBatch", "[Type] = 'NH'")
MsgBox NewBatch
LastBatch1 = NewBatch + 1
MsgBox LastBatch1
End Sub

Thanks for any suggestions in advance

[June7]

Why do you want this? Saving calculated data can be risky especially when there is no raw data to support the saved value. What if you accidentally repeatedly click the button?

Options:

1. move to the desired record on a form

2. use sql UPDATE action to modify the record in table: CurrentDb.Execute "UPDATE LastBatch SET BatchNum=" & NewBatch & " WHERE Type='NH'"

[bimcompu]

Thank you for the quick responce. It is nice to know that there are people out there that still care about other people and their problems. Here is what I am trying to accomplish:

I am the keeper of an inventory that has three departments. The database works fine and I have a form that automates most of the data entry for keeping tabs on all of the inventory. One of the fields in the main table is BatchNum.

Certain items in our inventory are placed in batches so that I don't have to count them on a monthly basis I can just look up the batch number that I placed on the batch itself and see the quantities.

What I am trying to accomplish is a running table of the last batch number and be able to increase that number by one every time I create a new batch. From there I export the data to a spreadsheet and create a batch ticket using merge in MS Word to place on the shelf with that batch.

I have all of it working except for the updating of the batch number.

I am going to try your suggestion when I get some time.

Thanks again!

[bimcompu]

That suggestion worked perfectly!! Now one more thing: Is it possible to place a variable in place if the hard coded "FN"? I can make it work hard coded if I duplicate the code for every department or create a variable with a list box.

I tried:

using Whatever as the variable from the list box

CurrentDb.Execute "UPDATE LastBatch SET BatchNum=" & NewBatch & " WHERE Type=" & whatever - Didn't work

CurrentDb.Execute "UPDATE LastBatch SET BatchNum=" & NewBatch & " WHERE Type= whatever - Didn't work

CurrentDb.Execute "UPDATE LastBatch SET BatchNum=" & NewBatch & " WHERE Type=" & [Whatever]

[June7]

Yes, can use a variable. How do you want to populate the variable? Have user select item from combo or list box? Then code can reference the listbox - the listbox is the variable.

CurrentDb.Execute "UPDATE LastBatch SET BatchNum=" & NewBatch & " WHERE Type='" & Me.listboxname & "'"

[bimcompu]

Perfect! Thanks for the help. My batch program now works and is keeping tabs on everything! Thanks again!