Autofill using info in a table (1 Viewer)

[Del]

I am building a data base where the initial info is entered in one table (manufactured date, model number, serial number and ship date)
I have then created a table where service on a product is recorded. I want to auto fill the model number, the manufactured date and the ship date in the service table based on the serial number of the item. Does any one know if this is possible in Acces? If so how would I accomplish this task

 

[Travis]

Try this:

On your Service Form add this code to the AfterUpdate of the Serial Number Field.

Private SerialNumber_AfterUpdate()
Dim rst as Recordset
Set rst = CurrentDb.OpenRecordset ("Select ModelNumber, ManufacturedDate, ShipDate From tblOne Where SerialNumber = " & Me.SerialNumber)
Me.ModelNumber = rst.Fields("ModelNumber")
Me.ManufacturedDate = rst.Fields("ManufacturedDate")
Me.ShipDate = rst.Fields("ShipDate")
Set rst = Nothing
End Sub

Each time the SerialNumber field is updated it will bring the other information over to the fields.

Another way to do this is to make the SerialNumber Field a combo box with the Model, Man Number and Ship Date as extra columns. and making the AfterUpdate Event look like this:

Private Sub SerialNumber_AfterUpdate()
Me.ModelNumber = Me.SerialNumber.Column(1)
Me.ManufactureNumber = Me.SerialNumber.Column(2)
Me.ShipDate = Me.SerialNumber.Column(3)
End Sub

As you may notice the second option has far less code and also will not have much overhead. Also to it's advantage is that you may not have to do more then just select the serial number from the list.

Hope these ideas help

 

[Pat Hartman]

You should not be duplicating data in the the service table. It is a violation of the rules of relational database design and could lead to inconsistant data. You can retrieve the information from the the main table via a join any time you need to display it.