inserting data automatically into a form based on other data

[smurf01]

I have a database that has three fields relating to customer details, what i want to do is create an input form where when i enter from the "spec Number" it automatically enters data into the field "customer" and the field "cust ref". Can someone tell me how to do this please. Example of data below

spec No customer cust ref
KT63798 KETTLE RM001762
KT63799 KETTLE RM001796
KT63800 KETTLE RM001765
KT63803 KETTLE RM001769
KT63804 KETTLE RM001775
KT63805 KETTLE RM001797

Regards

Paul

 

[Dalain]

There are a few ways to do this

1. If you are adding a new record to a table. You can define the Default Values in the Form Open event. The Default Values comes from an already Opened Form we will call "OpenedFrm"

Example: Input Form Module

Private Sub Form_Open(Cancel As Integer)
'Default Values
Me![customer].DefaultValue = [Forms]![OpenedFrm]![customer]
Me![cust ref].DefaultValue = [Forms]![OpenedFrm]![cust ref]

End Sub


2. If the record already exists in the table and you are only adding info to one of the fields, you can set a filter on the form. This will only show one record and again, you use the data from an already opened Form to set the Filter.

Example: OpenedFrm Form Module

Private Sub CommandButton_Click()
Dim stLinkCriteria As String

stLinkCriteria = "customer = """ & [Forms]![OpenedFrm]![customer] & """ And [cust ref] = """ & [Forms]![OpenedFrm]![cust ref] & """"

DoCmd.OpenForm “MyInputForm” , , , stLinkCriteria
End Sub

Note: Ensure the two fields are Locked; you don’t want the user to change the data in these fields.


Pierre

 

[young]

You can use SQL select statement to select the record which match "spec Number" (=me.MBR_ID)and use recordset to auto fill the rest control in the form. Bellow is partial code.

strSQL = "select * from [dbo_MEMBER] where [MBR_ID] = '" & Me.MBR_ID & "'"

Set dbs = CurrentDb()
Set rec = dbs.OpenRecordset(strSQL, dbOpenDynaset)

If rec.EOF And rec.BOF Then
Exit Sub (no record)
Else

DoCmd.SetWarnings True

Me.MEM_F_NAME = rec!F_NAME
Me.MEM_L_NAME = rec!L_NAME
Me.DOB = rec!DOB
Me.SEX = rec!SEX

.....