Autofill using form to populate data

[Helder]

I'm trying to develop a database to log inventory that is has unique serial
number but incremented in numbers of 25.

I what a form that allows the user to input the starting number, the number
of items and have access populate that data based on the users entry.

For example: starting number = 100
number of items= 5
autofill table with 100, 125, 150, 175, 200
This autofill will be my unique id's for the product

In the end, I would like a similar forms that allows the user to specify the
number of items taken out of inventory in the same manner but without a
starting number. I would like the database to recognize what is still in
inventory and select the next 2 items in sequence.

For example: user chooses 2 items out of inventory
access looks up what is available and removes from
inventory

I can't seem to find anything describing the first function which is my goal.
Any suggestions.

 

[Steve]

Make it easy on yourself! Access can automatically create unique IDs when
you enter products. Set up your product table like this:
TblProduct
ProductID
ProductName
....other fields you need regarding a product ....

Make ProductID an Autonumber data type and Access will automatically assign
an unique ID to this field whenever you enter products.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[Chris2]

I'm trying to develop a database to log inventory that is has unique serial
number but incremented in numbers of 25.

I what a form that allows the user to input the starting number, the number
of items and have access populate that data based on the users entry.

For example: starting number = 100
number of items= 5
autofill table with 100, 125, 150, 175, 200
This autofill will be my unique id's for the product

In the end, I would like a similar forms that allows the user to specify the
number of items taken out of inventory in the same manner but without a
starting number. I would like the database to recognize what is still in
inventory and select the next 2 items in sequence.

For example: user chooses 2 items out of inventory
access looks up what is available and removes from
inventory

I can't seem to find anything describing the first function which is my goal.
Any suggestions.

Helder,

FYI, I'm using Access 2000 (these descriptions aren't going to match
well with Access 2007).

------------------------------------

Create a Form and name it frmForm1.

Set the Record Source of frmForm1 to the name of your table.

On the detail area of frmForm1 draw a subform and name it
frmForm1_subForm1. When you go through the subform wizard, pick the
table and columns you need, and use all other defaults.

On frmForm1_subForm1, go to the property page, data tab, and change
the Source Objecte to Query.qdfQuery1.

On the property page, data tab, of frmForm1_subForm1, make sure Link
Child Fields and Link Master fields are blank.

In the detail area of frmForm1, draw labels and text boxes for the
starting number and number of items (lblStartingNumber,
txtStartingNumber, lblNumberOfItems, txtNumberOfItems).

Set the Default Value (property page, data tab) to "= 0" (leave out
the quotes) for both txtStartingNumber and txtNumberOfItems.

In detail area of frmForm1, draw a command button and name it
cmdSearch.

In the On Click property of the cmdSearch (property page, event tab),
type out [Event Procedure] (or select it using the drop-down options).

Select cmdSearch on frmForm1 and click on the code icon on the
toolbar. (If you don't know which button it is, hover your mouse over
the buttons until you get a tooltip that says "Code".)

You should now be in the Visual Basic Editor with the code module for
frmForm1 open

Use the (General) drop down box and select cmdSearch

You should now see:

Private Sub cmdSearch_Click()

End Sub


Add the following code:

Form_frmForm1.Requery


You should now see:

Private Sub cmdSearch_Click()

Form_frmForm1.Requery

End Sub

Click Cntl+S to save that.



Go back to the MS Access window.


Create a Query and name it qdfQuery1.

Copy and paste the following SQL into the query.

NOTE: You must alter the names below for your table and column names,
or it will not work.

SELECT YT1.StartingNumber
,YT1.<your second column>
,YT1.<your third column>
FROM <your table name> AS YT1
WHERE (YT1.StartingNumber >=
CInt([Forms]![frmForm1]![txtStartNumber]))
AND YT1.StartingNumber <=
(CInt([Forms]![frmForm1]![txtStartNumber]) +
(CInt([Forms]![frmForm1]![txtNumberOfItems]) * 25));



It should be ready to go.

When I run frmForm1, it pops up and the subform is blank.

I add numbers into txtStartingNumber and txtNumberOfItems, click the
cmdSearch button, and the subform populates as specified.


----------------------------


I hope that works out for you.


----------------------------


Possibly Unnecessary Commentary:

This is a Forms question in the Tables and DB Design newsgroup. In
the future, you may get more numerous (and possibly more skilled)
answers by posting Forms questions there.


Sincerely,

Chris O.

 

[Jeff Boyce]

Why? As in "why do you feel you need to increment in 25s?" What business
need do you believe you are solving by doing that? I ask, not out of idle
curiosity, but because if we better understood the problem you are trying to
solve this way, we might be able to offer alternate solutions.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Helder]

Chris,
My apologees for posting this question in the wrong forum. I'm rather new
at this and didn't realize that design meant only the table and Db design. I
thought although I wanted a created to meet my needs it was ultimately a DB
design that would do the trick.

I'm trying to develop a database to log inventory that is has unique serial
number but incremented in numbers of 25.

I what a form that allows the user to input the starting number, the number
of items and have access populate that data based on the users entry.

For example: starting number = 100
number of items= 5
autofill table with 100, 125, 150, 175, 200
This autofill will be my unique id's for the product

In the end, I would like a similar forms that allows the user to specify the
number of items taken out of inventory in the same manner but without a
starting number. I would like the database to recognize what is still in
inventory and select the next 2 items in sequence.

For example: user chooses 2 items out of inventory
access looks up what is available and removes from
inventory

I can't seem to find anything describing the first function which is my goal.
Any suggestions.

Helder,

FYI, I'm using Access 2000 (these descriptions aren't going to match
well with Access 2007).

------------------------------------

Create a Form and name it frmForm1.

Set the Record Source of frmForm1 to the name of your table.

On the detail area of frmForm1 draw a subform and name it
frmForm1_subForm1. When you go through the subform wizard, pick the
table and columns you need, and use all other defaults.

On frmForm1_subForm1, go to the property page, data tab, and change
the Source Objecte to Query.qdfQuery1.

On the property page, data tab, of frmForm1_subForm1, make sure Link
Child Fields and Link Master fields are blank.

In the detail area of frmForm1, draw labels and text boxes for the
starting number and number of items (lblStartingNumber,
txtStartingNumber, lblNumberOfItems, txtNumberOfItems).

Set the Default Value (property page, data tab) to "= 0" (leave out
the quotes) for both txtStartingNumber and txtNumberOfItems.

In detail area of frmForm1, draw a command button and name it
cmdSearch.

In the On Click property of the cmdSearch (property page, event tab),
type out [Event Procedure] (or select it using the drop-down options).

Select cmdSearch on frmForm1 and click on the code icon on the
toolbar. (If you don't know which button it is, hover your mouse over
the buttons until you get a tooltip that says "Code".)

You should now be in the Visual Basic Editor with the code module for
frmForm1 open

Use the (General) drop down box and select cmdSearch

You should now see:

Private Sub cmdSearch_Click()

End Sub


Add the following code:

Form_frmForm1.Requery


You should now see:

Private Sub cmdSearch_Click()

Form_frmForm1.Requery

End Sub

Click Cntl+S to save that.



Go back to the MS Access window.


Create a Query and name it qdfQuery1.

Copy and paste the following SQL into the query.

NOTE: You must alter the names below for your table and column names,
or it will not work.

SELECT YT1.StartingNumber
,YT1.<your second column>
,YT1.<your third column>
FROM <your table name> AS YT1
WHERE (YT1.StartingNumber >=
CInt([Forms]![frmForm1]![txtStartNumber]))
AND YT1.StartingNumber <=
(CInt([Forms]![frmForm1]![txtStartNumber]) +
(CInt([Forms]![frmForm1]![txtNumberOfItems]) * 25));



It should be ready to go.

When I run frmForm1, it pops up and the subform is blank.

I add numbers into txtStartingNumber and txtNumberOfItems, click the
cmdSearch button, and the subform populates as specified.


----------------------------


I hope that works out for you.


----------------------------


Possibly Unnecessary Commentary:

This is a Forms question in the Tables and DB Design newsgroup. In
the future, you may get more numerous (and possibly more skilled)
answers by posting Forms questions there.


Sincerely,

Chris O.

 

[Helder]

To answer your question Jeff,
The business need is to tightly control receipt books which are issued and
log at 25 increment per book. Each receipt book only contains 25 receipts,
cash receipts, and there are all sequencial. Our business conducts many in
home sales and we want to log these books quite tightly.

I thought of using Access and unique # function but in an audit purpose I
thought it better to use the actual number of the receipt book. There would
be no confusion.
I've previously used Excel for the logging but found it time consuming for
logging shipping, lapsed time, and return logging.

Also we send to our various locations an annual audit report. If we could
have all the inputs done through Access using the actual receipt numbers the
whole process might be faster and air tight.

Hope I answered your question.
Thanks,
Helder

 

[Chris2]

Chris,
My apologees for posting this question in the wrong forum.

It's not too important (to me, anyway). That's why I put my comment
at the bottom and included, "in the future".

I'm rather new
at this and didn't realize that design meant only the table and Db

design.

Again, no worries.

I thought although I wanted a created to meet my needs it was ultimately
a DB design that would do the trick.

I am not sure I follow what you're saying, but I hope your question
was resolved.


Sincerely,

Chris O.