Inventory Database Help (multiple serial numbers, same part numbers)

[DNASok]

Upfront - I am knew to Access and this is my first build attempt.

I have a requirement to create a database to track our equipment inventory. The inventory is being saved to an "Asset Tracking" table. The fields this table contain include:

1. Item Number (primary key)
2. Category (i.e. Electronics, Mechanics)
3. Equipment (i.e. Computer, Engine)
4. Part Number
5. Serial Number
6. Date IN
7. Location

I have created an "Input Item to Storage" form which correctly allows the user to input a single item at a time. However, what I would like to do is allow the user to input multiple serial numbers at one time with the category/equipment/part number/date in/location remaining constant.
For example, the user would input the Category, Equipment Nomenclature, Part Number, Date In, and Location; with multiple Serial Numbers.
The issue with what I have is that we have deliveries for 20-50+ of a single device which is required to be serially tracked. What I have requires the user to input the same data for all 20-50. This is not optimal to say the least.

 

[plog]

2 methods I can think of:

1. Don't use a form, use a spreadsheet. Create a column in the spreadsheet for every field of your table (excluding the Item Number if it is an autonumber) and enter 1 row of data. Then copy that row of data however many times you need and then then go through it row by row entering correct serial numbers. When done you import that file into your table.

2. Using your form create a 'Copy Record' button that executes a INSERT query that creates a new record in the table using the data currently showing on the form. Then the form updates and goes to that new record so that the serial number (or any other field) can be edited. The user just keeps clicking that button and changing serial numbers until done.

 

[spikepl]

If your table indeed contains the fields you listed, I'd suggest you first normalize your data. Read up on normalization. The information you listed belongs in several tables, if data is to be normalized. Once you split it accordingly, I believe the solution to your problem will come to you.

 

[missinglinq]

For each Field you want to 'carry forward' do this in the AfterUpdate event. The data in a given Control the data will remain the same until you change it, manually, or close the Form.

Private Sub ControlName_AfterUpdate()
  Me.ControlName.DefaultValue = """" & Me.ControlName.Value & """"
End Sub

Linq ;0)>

 

[DNASok]

For each Field you want to 'carry forward' do this in the AfterUpdate event. The data in a given Control the data will remain the same until you change it, manually, or close the Form.

Private Sub ControlName_AfterUpdate()

[B] Me.ControlName.DefaultValue = """" & Me.ControlName.Value & """"[/B]
[B]End Sub[/B]

Linq ;0)>

I am getting an error stating "Compile error: Method or data member not found. It is also hightlighting the .ControlName

 

[missinglinq]

ControlName was just a generic name I used for the demo code! You need to replace it with the actual name of the Control (Textbox, Combobox, etc) whose value you want to 'carry forward.'

You'll need to do this for each of the Controls in question.

Linq ;0)>