populate new record with info from previous record !

[luvthisboard!]

Hope I explain this so that you can follow here is what I wish to do !

Form has the following fields (among others)

From: (number field)

To: (number field)

Number of items : (number field)

Here's what I want to be able to do.

From number should appear when form opens cause it is already populated from previous entry as follows:

User enters in the number of items that they want and database adds that to the From Number and comes up with the to number then when user goes to add new record the from number is the previous to number plus 1 (hope you get this!)

example:

From: 150
To: blank

Number of items: 30 (entered by user)

Then To: becomes 179

and From: on the next new record is 180

obviously I will also have to figure out what to do with the very first From: cos there won't be a previous record to take it from

hope you can follow many thanks

 

[wcrc]

Is this for keeping a running total number of items ordered? If so, then would an autonumber work here?

 

[luvthisboard!]

sort of !

I need to keep a record of

A) the first number in the batch of items

b) the last number in the batch of items

C) The number of items !

I want it to be automated where possible to at least cut down on the human error bit, its for stock records. so what I want is for the user only to have to enter in how many items it wants and the rest to be automated and unchangeable, I want to be able to go to the physical stock see number 156 printed on it and then go to the system and see that number 156 is the next item to be used ! do you follow?

 

[luvthisboard!]

actually what I think I need is dlookup but can't figure how to get it to work properly?

 

[wcrc]

Just so I'm clear on this:

You have a set of numbers in a batch (example 1-100), and if the user needs 30 items then you want to give the user item number 1-30.

If that is the case how do you track each batch of items?

Is this an open ended system that will never restart or will it restart at one when you replenish the stock?

Sorry about all the questions I just want to understand this better, so I can help.

 

[luvthisboard!]

Thanks for your reply I'll try and explain !

ok there is really only 1 batch cos we only have 1 stock item and it's not divided into specific batches, my use of the word batches may have been misleading.

We have 1 stock item in which each physical piece of material has an individual (sequential) number printed on it, (like an invoice book say anyway when the user sends out a batch or a bundle of these to a customer they need to record the info in a database.

in order to cutdown on the possiblity of human error I want to automate the process as much as possible so ! what I want really is for the user to just enter the number of items the customer wants and the database to add that to the "from" number get the"to" number which plus 1 then becomes the "from" number on the next record (the next lot they need to send out !) I think maybe a dlookup function combined with something else might work? but I can't figure out the dlookup at all !. I hope you can follow cos it'd doing my head in ! I'm a keen access user with a bit of cop op but I'm far from being an expert which is obviously becoming clear now !

 

[jfgambit]

Can you describe your table structure? Is there a Yes/No Column the keeps users from getting multiple Stock numbers?

For example:

Stocknumber (unique ID)
Used (yes/No)

If so, then you need to not only utilze a DMin, but an Update as well to set the Value of Used to 'YES'.

Example:
Added to the After Update Event of the Number of Items textbox

Dim rsStk as Variant
Dim db as Database

'Find the minimum number that is not Used
rsStk = DMin("[StockNumber]","tblStockNumbers","[Used] = " & False)
'Add the Number of items to the rsStk number and update From
Me.To = rsStk + me.NumberofItems
db = Currentdb()
'Update all Used between From and To to TRUE(YES)
db.Execute "UPDATE tblStockNumbers SET tblStockNumbers.StockNumber = True " & _
"WHERE (tblStockNumbers.StockNumber) Between " & me.From & " AND " & me.To & ";"

Haven't tried the syntax above in a module, but let us know if there is a problem.

HTH

 

[luvthisboard!]

AHHH I think I might be in over my head !! I haven't got a table structure yet cos I wanted to wait until I got the replies to see if it would help me put it together from scratch !! I'll have to take some time to digest your answer and see if I can figure it out ! thanks for taking the time to reply

 

[jfgambit]

AHHHH...yes that might be a problem.

You might consider the following tables

tblProduct
ProductID (PK) - Number
ProductName - Text
ProductSpecs - Text

tblStockNumber
StockNumber (PK) - Number
ProductID - Number (Linked one-to-many to tblProducts)
Used - Yes/No
Assignedto - Number (Linked one-to-many to tblUsers)

tblUsers
UserID (PK) - Number
FName - Text
LName - text
PhNbr - Text


HTH