Need some advice on making a form with 2 text boxes that will contain

numbers.

One number will be a starting number for inventory/serial numbers to be

entered in stock...the second number will be the last number.



I need to know if/how this can be done for inventory entry.



For example, I have a table with these fields



StockDate    Description    InventoryNumber

10/18/01        Crowbar        0810001

10/18/01        Crowbar        0810002

etc...



instead of entering each item manually via a form, I want to list the

numbers from and to...click a button, and have it populate my inventory

table with the description.



Thanks in advance,

Darin

Consider breaking this table in two--one with just stock date, description &

a primary key, and the other with SN and a foreign key to the first table.

That way you don't have to programmatically add those values & if they ever

need to be edited, your user can do it on just one record.



To programmatically fill in the SNs from a start and end number, you may be

able to use a For-Next loop.  For instance:



(warning: air code!)



Dim i as Integer



For i = CLng(Me.txtLowNumber) To CLng(Me.txtHiNumber)

   DoCmd.RunSQL "INSERT INTO tblSerialNumbers (StockID, SN) VALUES (" &

Me.StockID & ", " & CStr(i) & ") ;"

Next i



Hope that helps.



Cheers,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

(xxx) xxx-xxxx

This code sample will automatically assign a InventoryNumber (that will 

increment by 1), assign the StockDate, and Description.  Now you can use a 

combo box to select the 

Description for accuracy.



Just copy this code inside the Click Event of a command button.



''*****************************************



DoCmd.Save

Dim db As Database

Dim rec As Recordset

Dim str

Dim intComp1 As Long

Dim intComp2 As Long



str = "Inventory"



Set db = CurrentDb()

Set rec = db.OpenRecordset(str)



If rec.BOF = True Then

    intComp1 = 810001

Else

    DoCmd.GoToRecord , , acFirst

    intComp1 = Me.InvNum

    rec.MoveFirst

    Do Until rec.EOF

        intComp2 = rec("InvNum")

        If intComp1 = intComp2 Then

            intComp1 = intComp1 + 1

            rec.MoveNext

        Else

            rec.MoveNext

        End If

    Loop

End If



rec.Close

db.Close



MsgBox "This is the new Inventory Number: " & intComp1



DoCmd.GoToRecord , , acNewRec



Me.InvDate.Value = Date

Me.ItemDesc.Value = "Crossbar"

Me.InvNum.Value = intComp1



DoCmd.Save



''*****************************************

Hint;  There is one glitch to this code.  the first time around works fine 

but make sure that you navigate back to the first recordset to save and 

refresh data.

After this small intervention (2nd record only), everything should work 

normally.



Good Luck,





Derrick A. Flores

Sony Semiconductor

Assoc. Systems Engineer

Darin,



The following code is assuming your InventoryNumber field is a numeric

type.  If it is a text type, then the code can be modified accordingly.



1. Create a query to find the Max Inventory Number. For this example I'm

calling the query "qryFindMaxInvNo"



2. Create a query to find the products that do not have Inventory

Numbers. For this example I'm callling the query "qryItemsWithNo_InvNo"



3. Create a button on the form to "Generate Inventory Numbers". On the

button's OnClick() event add the following



Private Sub cmdNewInvNo_OnClick()

	Dim rs as DAO.Recordset

	Dim dblIDNo as double



	set rs = CurrentDB.OpenEecordset("qryFindMaxInvNo")

	

	dblIDNo = rs!MAXInventoryNumber

	

	set rs=currentdb.openrecordset("qryItemsWithNo_InvNo")

	

	do while not rs.eof

		dblIDNo = dblIDNo + 1

		rs.edit

			rs!InventoryNumber=dblIDNo

		rs.update	

		rs.movenext

	loop



	rs.close

	set rs = Nothing



End Sub



This should do the trick without having the user enter the

InventoryNumber From and To range.





If you want the user to enter an InventoryNumber range, then;



1. Create a query to find the products that do not have Inventory

Numbers. For this example I'm callling the query "qryItemsWithNo_InvNo"



2. Create a button on the form to "Generate Inventory Numbers". On the

button's OnClick() event add the following



3. I'm assuming the text boxes on the form are called

txtFirstInventoryNo and txtLastInventoryNo



Private Sub cmdNewInvNo_OnClick()

	Dim rs as DAO.Recordset

	Dim dblCounter as double



	dblCounter=0

	set rs=currentdb.openrecordset("qryItemsWithNo_InvNo")

	

	do while not rs.eof

		dblCounter=dblCounter+1

		if dblCounter=1 then

			rs.edit

				rs!InventoryNumber=txtFirstInventoryNo

			rs.update	

		elseif txtStartInventoryNo + dblCounter <

txtLastInventoryNo

			rs.edit

	

rs!InventoryNumber=txtFirstInventoryNo+dblCounter

			rs.update			

		rs.movenext

	loop



	rs.close

	set rs = Nothing



End Sub

Thanks for the help, John.



Darin

Thanks Roy,



I've "inherited" this database...and I'm getting ready to rebuild an access

project, so I'm leaving the current structure alone :)  otherwise, I would

split this table in two....I'd actually split many tables in two, hehe.



Darin





----- Original Message -----

From: "Pardee, Roy E" <roy.e.pardee@l...>

To: "Access" <access@p...>

Sent: Thursday, October 18, 2001 4:02 PM

Subject: [access] Re: Automatic row population





> Consider breaking this table in two--one with just stock date, description

&

> a primary key, and the other with SN and a foreign key to the first table.

> That way you don't have to programmatically add those values & if they

ever

> need to be edited, your user can do it on just one record.

>

> To programmatically fill in the SNs from a start and end number, you may

be

> able to use a For-Next loop.  For instance:

>

> (warning: air code!)

>

> Dim i as Integer

>

> For i = CLng(Me.txtLowNumber) To CLng(Me.txtHiNumber)

>    DoCmd.RunSQL "INSERT INTO tblSerialNumbers (StockID, SN) VALUES (" &

> Me.StockID & ", " & CStr(i) & ") ;"

> Next i

>

> Hope that helps.

>

> Cheers,

>

> -Roy

>

> Roy Pardee

> Programmer/Analyst

> SWFPAC Lockheed Martin IT

> (xxx) xxx-xxxx

>

>

Thanks for the help,



I've got several ideas to try, now!



Darin





----- Original Message ----- 

From: "Derrick Flores" <Derrick_Flores@s...>

To: "Access" <access@p...>

Sent: Thursday, October 18, 2001 4:39 PM

Subject: [access] Automatic row population





This code sample will automatically assign a InventoryNumber (that will 

increment by 1), assign the StockDate, and Description.  Now you can use a 

combo box to select the 

Description for accuracy.



Just copy this code inside the Click Event of a command button.



''*****************************************



DoCmd.Save

Dim db As Database

Dim rec As Recordset

Dim str

Dim intComp1 As Long

Dim intComp2 As Long



str =Inventory"



Set db =urrentDb()

Set rec =b.OpenRecordset(str)



If rec.BOF =rue Then

    intComp1 =10001

Else

    DoCmd.GoToRecord , , acFirst

    intComp1 =e.InvNum

    rec.MoveFirst

    Do Until rec.EOF

        intComp2 =ec("InvNum")

        If intComp1 =ntComp2 Then

            intComp1 =ntComp1 + 1

            rec.MoveNext

        Else

            rec.MoveNext

        End If

    Loop

End If



rec.Close

db.Close



MsgBox "This is the new Inventory Number: " & intComp1



DoCmd.GoToRecord , , acNewRec



Me.InvDate.Value =ate

Me.ItemDesc.Value =Crossbar"

Me.InvNum.Value =ntComp1



DoCmd.Save



''*****************************************

Hint;  There is one glitch to this code.  the first time around works fine 

but make sure that you navigate back to the first recordset to save and 

refresh data.

After this small intervention (2nd record only), everything should work 

normally.



Good Luck,





Derrick A. Flores

Sony Semiconductor

Assoc. Systems Engineer

Hey everyone, I asked about auto populating fields...inventory numbers...a

while back, and had some great feedback.  The solution I went with was one

Roy recommended, and I have it all working now except for one minor bug.

The data type for field CNUM is text...which is what the value from "inv"

should be.



Private Sub cmdUpdate_Click()

Dim inv

For inv = Forms!frmInventory!txtFrom To Forms!frmInventory!txtTo

    DoCmd.RunSQL "INSERT INTO INVENTORY

(InvoiceID,DT,Office,Description,CNUM) VALUES

(Forms!frmInventory!INVOICEID,Forms!frmInventory!DT,Forms!frmInventory!OFFIC

E,Forms!frmInventory!DESCRIPTION," & Format$(inv, "0000000") & ");"



Next inv

End Sub





Everything is inserting fine now!  Only bug I have to figure out is...all

our serial numbers are 7 digits...the majority of them start with a 0.  And,

I need to figure out how to get that 0 on the front of the number when it

enters into the table.  I tried using several pads of 0's as you

recommended, but it never did work.  For example, currently if I enter the

number 0110011 it populates as 110011.  Any thoughts?  Hehe, I won't bother

you anymore about this.



Darin

I think I see the problem--if CNUM is text, you've got to delimit the value

you're inserting with quotes.  Try:



    DoCmd.RunSQL "INSERT INTO INVENTORY

(InvoiceID,DT,Office,Description,CNUM) VALUES

(Forms!frmInventory!INVOICEID,Forms!frmInventory!DT,Forms!frmInventory!OFFIC

E,Forms!frmInventory!DESCRIPTION, '" & Format$(inv, "0000000") & "');"



instead & see if that works.



Cheers,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487



-----Original Message-----

From: Darin Wray [mailto:darin@r...]

Sent: Wednesday, October 24, 2001 11:25 AM

To: Access

Subject: [access] Re: Automatic row population





Hey everyone, I asked about auto populating fields...inventory numbers...a

while back, and had some great feedback.  The solution I went with was one

Roy recommended, and I have it all working now except for one minor bug.

The data type for field CNUM is text...which is what the value from "inv"

should be.



Private Sub cmdUpdate_Click()

Dim inv

For inv = Forms!frmInventory!txtFrom To Forms!frmInventory!txtTo

    DoCmd.RunSQL "INSERT INTO INVENTORY

(InvoiceID,DT,Office,Description,CNUM) VALUES

(Forms!frmInventory!INVOICEID,Forms!frmInventory!DT,Forms!frmInventory!OFFIC

E,Forms!frmInventory!DESCRIPTION," & Format$(inv, "0000000") & ");"



Next inv

End Sub





Everything is inserting fine now!  Only bug I have to figure out is...all

our serial numbers are 7 digits...the majority of them start with a 0.  And,

I need to figure out how to get that 0 on the front of the number when it

enters into the table.  I tried using several pads of 0's as you

recommended, but it never did work.  For example, currently if I enter the

number 0110011 it populates as 110011.  Any thoughts?  Hehe, I won't bother

you anymore about this.



Darin

Roy,



Many thanks on working thru this with me.  This worked perfect!



Darin





----- Original Message -----

From: "Pardee, Roy E" <roy.e.pardee@l...>

To: "Access" <access@p...>

Sent: Wednesday, October 24, 2001 4:53 PM

Subject: [access] Re: Automatic row population





> I think I see the problem--if CNUM is text, you've got to delimit the

value

> you're inserting with quotes.  Try:

>

>     DoCmd.RunSQL "INSERT INTO INVENTORY

> (InvoiceID,DT,Office,Description,CNUM) VALUES

>

(Forms!frmInventory!INVOICEID,Forms!frmInventory!DT,Forms!frmInventory!OFFIC

> E,Forms!frmInventory!DESCRIPTION, '" & Format$(inv, "0000000") & "');"

>

> instead & see if that works.

>

> Cheers,

>

> -Roy

>

> Roy Pardee

> Programmer/Analyst

> SWFPAC Lockheed Martin IT

> Extension 8487

>

> -----Original Message-----

> From: Darin Wray [mailto:darin@r...]

> Sent: Wednesday, October 24, 2001 11:25 AM

> To: Access

> Subject: [access] Re: Automatic row population

>

>

> Hey everyone, I asked about auto populating fields...inventory numbers...a

> while back, and had some great feedback.  The solution I went with was one

> Roy recommended, and I have it all working now except for one minor bug.

> The data type for field CNUM is text...which is what the value from "inv"

> should be.

>

> Private Sub cmdUpdate_Click()

> Dim inv

> For inv = Forms!frmInventory!txtFrom To Forms!frmInventory!txtTo

>     DoCmd.RunSQL "INSERT INTO INVENTORY

> (InvoiceID,DT,Office,Description,CNUM) VALUES

>

(Forms!frmInventory!INVOICEID,Forms!frmInventory!DT,Forms!frmInventory!OFFIC

> E,Forms!frmInventory!DESCRIPTION," & Format$(inv, "0000000") & ");"

>

> Next inv

> End Sub

>

>

> Everything is inserting fine now!  Only bug I have to figure out is...all

> our serial numbers are 7 digits...the majority of them start with a 0.

And,

> I need to figure out how to get that 0 on the front of the number when it

> enters into the table.  I tried using several pads of 0's as you

> recommended, but it never did work.  For example, currently if I enter the

> number 0110011 it populates as 110011.  Any thoughts?  Hehe, I won't

bother

> you anymore about this.

>

> Darin