Append a range 1

[michaelhutcheson]

Hello,

Is it possible to append a range of numbers to a table? For example:

3 fields - Part, Qty and Serial No.

User selects a Part and enter a qty, eg Part: ABC and Qty: 10

Then I need access to work out and append the Serial number and the Part and Qty. However, the serial is based on the Qty. So for my example the table would end up looking like:

Serial Part Qty
1 ABC 10
2 ABC 10
3 ABC 10
4 ABC 10
5 ABC 10
6 ABC 10
7 ABC 10
8 ABC 10
9 ABC 10
10 ABC 10

However, when they come along to select the part, and choose a qty of say 5, the serail would start at 15 and go to 20.

Is this possible?

Michael

 

[dhookom]

What happened to serials 11 to 14? Wouldn't the quantity be 1 for each of these records? This can all be done (I think) beginning with a table of all numbers and some standard SQL.

Duane
Hook'D on Access
MS Access MVP

 

[michaelhutcheson]

the serial number is based on the qty. so if you choose a qty of 1, it would the serial number would be 1. then the next user enters a serial of 10 the serial numbers would be 2 - 12 etc

 

[dhookom]

So your first post was wrong in that the next 5 should have begun with 11 and not 15?

Have you created a table of all numbers? If not, start by creating tblNums with a single numeric field [Num]. Then add 1 through the maximum quantity.

Then, depending on where the Part Number and Quantity come from, you can create an append query that uses DMax("Serial","tblYourTable") + Num to generate the new serials. Set the criteria under the Num field to
Between 1 and [Your Quantity Expression]

Duane
Hook'D on Access
MS Access MVP

 

[michaelhutcheson]

mmmm I don't fully understand I'm afraid. Where do I put the DMax formula?

 

[dhookom]

What are your significant table and field names with some sample data? Have you created a tblNums? How/where do you expect users to enter the Part Number and Quantity?

What exactly would a user enter and what exact records would be added to your table?

Duane
Hook'D on Access
MS Access MVP

 

[michaelhutcheson]

Ok, I have a table called Num that holds numbers from 10000 - 99999999 or however many and also has a yes\no field called 'taken' Then I have the below form:

Date: [ ] User enters the date

Part: [ ] This is a look up from a table that is linked to our ERP system

Serial: [ ] this is subform linking to a query based on the Num table. It looks for the minimum number where the 'taken' flag is 'no'

Qty [ ] User enters the qty


So, the user enters the data below:

Date: [01/03/10]
Part: [123456]
Serial is displaying 100000
Qty [5]

Now, how do I append the following to a seperate table:

Serial Part Date
100000 123456 01/03/10
100001 123456 01/03/10
100002 123456 01/03/10
100003 123456 01/03/10
100004 123456 01/03/10

It will also mark 100000, 100001, 100002, 100003 and 100004 in the num table as taken, but I can sort that.

Thanks.

 

[MajP]

Public Sub insertParts(SerialNo, part, dtmDate, quantity)
 
  Dim intCount As Integer
  Dim strSql As String
  
  strSql = "Insert into tblNewTable (SerialNo,Part,dtmDate) values "
  strSql = strSql & "(" & SerialNo & ",'" & part & "',#" & dtmDate & "#)"
  Debug.Print strSql
  For intCount = 1 To quantity
     strSql = "Insert into tblNewTable (SerialNo,Part,dtmDate) values "
     strSql = strSql & "(" & SerialNo & ",'" & part & "',#" & dtmDate & "#)"
     CurrentDb.Execute strSql
     SerialNo = SerialNo + 1
  Next intCount

modify to fit your names.
Do not name a field Date because it is a reserved word.

 

[dhookom]

You missed a number of significant names of forms, fields, and tables. Try an append query like:

INSERT INTO [A separate table] (Serial, Part, [Date])
SELECT [Holds Numbers], Forms![below]!Part, Forms![below]![date] 
FROM Num WHERE [Holds Numbers] Between Forms![below]![Serial] and Forms![below]![Serial] + Forms![below]![Qty]

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

You missed a number of significant names of forms, fields, and tables

Unless I am missing something, I do not think I missed anything.

So, the user enters the data below:

Date: [01/03/10]
Part: [123456]
Serial is displaying 100000
Qty [5]

So from the form

Private sub some event()
call insertParts(me.txtSerial,Me.txtPart,me.Serialdisplay,me.txtQuan)
end sub


And unless I am missing something, I do not even get the table HoldNumbers. Is the OP just incrementing from the max serial?

 

[dhookom]

MajP,
My response was to the OP.

Duane
Hook'D on Access
MS Access MVP

 

[michaelhutcheson]

I've chagned the names for code example but can't seem to get it to work. I amended the SQL statement but keep receiving the message "Characters found after end of SQL statement" and then it highlights the FROM statement.

Sorry, Access is not my strong point!

 

[michaelhutcheson]

I've tried this append to no avail:

INSERT INTO tblSerial ( [Date], Part, SerialNo )
SELECT [Forms]![frmSerial]![txtDate] AS Expr2, [Forms]![frmSerial]![cmbPart] AS Expr3, [tblNum]![Serial] AS Expr1
WHERE ((([tblNum]![Serial]) Between [Forms]![frmSerial]![qryMinNum subform].[Form]![MinOfSerial] And [Forms]![frmSerial]![qryMinNum subform].[Form]![MinOfSerial]+[Forms]![frmSerial]![txtQty]));

 

[michaelhutcheson]

Even just appending the serial number's doesn't work:

INSERT INTO tblSerial ( SerialNo )
SELECT [tblNum]![Serial] AS Expr1
WHERE ((([tblNum]![Serial]) Between [Forms]![frmSerial]![qryMinNum subform].[Form]![MinOfSerial] And [Forms]![frmSerial]![qryMinNum subform].[Form]![MinOfSerial]+[Forms]![frmSerial]![txtQty]));


Arrrrrrrrrrrrrrrrrrrrrrgh!!

 

[dhookom]

Can you elaborate on "to no avail"?
What do you see if you have all your forms open with the appropriate values and then create a query with

SELECT [Forms]![frmSerial]![txtDate] AS Expr2, [Forms]![frmSerial]![cmbPart] AS Expr3, [tblNum]![Serial] AS Expr1
WHERE ((([tblNum]![Serial]) Between [Forms]![frmSerial]![qryMinNum subform].[Form]![MinOfSerial] And [Forms]![frmSerial]![qryMinNum subform].[Form]![MinOfSerial]+[Forms]![frmSerial]![txtQty]));

How about opening the debug window (press Ctrl+G) and entering:

 ? Forms]![frmSerial]![qryMinNum subform].[Form]![MinOfSerial]

or

 ? [Forms]![frmSerial]![txtQty]

Duane
Hook'D on Access
MS Access MVP

 

[michaelhutcheson]

for your first one I received:

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

 

[dhookom]

You/we missed the FROM clause. Go back and review my posting from 1 Mar 10 12:05.

Duane
Hook'D on Access
MS Access MVP

 

[michaelhutcheson]

I'm lost. When I use the FROM statement I receive the message "Characters found after end of SQL statement" and then it highlights the FROM statement.

 

[dhookom]

Please reply back with your SQL view. Is this what you tried?

SELECT [Forms]![frmSerial]![txtDate] AS Expr2, [Forms]![frmSerial]![cmbPart] AS Expr3, [tblNum]![Serial] AS Expr1
FROM tblNum
WHERE ((([tblNum]![Serial]) Between [Forms]![frmSerial]![qryMinNum subform].[Form]![MinOfSerial] And [Forms]![frmSerial]![qryMinNum subform].[Form]![MinOfSerial]+[Forms]![frmSerial]![txtQty]));

Duane
Hook'D on Access
MS Access MVP

 

[michaelhutcheson]

You have solved it!!!!!!! Thank you so much for your help, you have made my day : -D