want to fill in all other fields in record when primary key is entered 1

[elviajero]

I have an update record form and I would like the user to be able to key in the serial number, my unique field, hit Tab, and have all the other fields on the form associated with that serial number, to fill out appropriately. The fields on the form all correspond to the fields in the one table I have where serial number is the primary key.

The idea is my user can specify a particular record to modify quickly. There are too many records for a dropdown list.

Is there any way to do this using a query or am I looking at using my rather rusty Visual Basic?

 

[blakex1]

I tried to do the exact same thing in my database.
I found the easiest way to do this is a find button.
Heres how it works.. they click the button, and a popup box apears and asks them for the record to locate (in your case it would ask them to enter the serial #)
VB takes the value creates a clone of the recordset and finds a corresponding value in the table, sets a bookmark and makes that record the active record.
It sounds awfully difficult but it is really simple when you break it down. Here is the code that does this:

Private Sub Command81_Click()
On Error GoTo Err_Command81_Click


Dim db As Database
Dim rec As DAO.Recordset

Dim serialnumber As String
Dim Tablename As String

Tablename = "Yourtablenamehere"

Set db = CurrentDb()
Set rec = db.OpenRecordset(Tablename)

serialnumber = InputBox("Please enter the Serial Number to update&quot

Me.RecordsetClone.FindFirst "[serialnumberfieldname] = '" & serialnumber & "'"
If rec.NoMatch Then
MsgBox "No matching CourtID found"
Else
Me.Bookmark = Me.RecordsetClone.Bookmark
End If
rec.Close

Exit_Command81_Click:
Exit Sub

Err_Command81_Click:
MsgBox Err.Description
Resume Exit_Command81_Click

End Sub

This should work with your table. Just cut and paste it in to the onclick event for a "find" command button, and change the table name and field name to match up with your names.

 

[elviajero]

Thanks! It worked. I really appreciate it, it's going to help me on several other forms as well.

Now I just have to figure out how to get an error message to come up if they enter an invalid serial number. Right now the window asking for serial number goes away and it just returns to the form if that happens.

 

[scking]

There is an alternate method that I prefer.

Use a combobox and in the RowSource property put in the SQL the will return all the information you would be using in the form.

SELECT DISTINCTROW tblParts.PN, tblPars.SN FROM tblParts;

Then, in the AfterUpdate code merely assign the values in the combobox.

Me.txtValue1 = Combo1.Column(0)
Me.txtValue2 = Combo1.Column(1)

Remember to hide the columns using the ColumnWidth property of the combobox. The following values would hide column(0) and show column(1) 0";1.9584"

Taking it one step further the better document the procedure you can use constants to represent the column number.

Private Const MyPartNumber As Integer = 0

Then use the following assignment:

Me.txtValue1 = Combo1.Column(MyPartNumber)

Steve King