Populate text box from combo box selection

[Pirie]

Hi,

I have a form which is to be used for editing/deleting data from a table and was wondering if it was possible to select which record from a combo box which would populate the textboxes on the form allowing me to edit the data?

If so, how do I do it?

Thanks

 

[missinglinq]

Create a form based on your table/query, including all the fields you want displayed. Then simply:

Add a combo box to your form. The Combobox Wizard will pop up

Select "Find a record based on the value I selected in my combobox."

From the table/query the form is based on, click on the field you're searching by (a field unique to each record) to move it to the right side.

Hit Next.

Size the column appropriately.

Hit Next.

Name the combobox.

Hit Finish.

Now you can drop the combobox down and scroll down to the item to search by, or you can start to enter the item, and the combobox will "autofill" as you type. Hit <Enter> and the record will be retrieved.

Linq

 

[RuralGuy]

Let the ComboBox Wizard create a ComboBox that will "Look up a record" for you. IIRC it is the third selection in the list if the form is bound to a RecordSource.

EDIT: Oops, Linq is just too fast!

 

[missinglinq]

Not bad for a gimp using two canes!

 

[Pirie]

Thanks missinglinq,

Got it working now,

Thanks a lot guys!

 

[Pirie]

One further question!

One another form I have an ID number field which is not autonumber as the field is part of a relationship. The form is to be used to enter a new record and I would like the number of the next record to be displayed in the textbox when the form is opened, how do I do this?

I've tried entering a count function but it doesn't seem to like it!

Any help?

 

[missinglinq]

You sound like you actually want to see it on opening, so if a single-user db:

Private Sub Form_Load()
If Me.NewRecord Then
   Me!RecordIDTextbox = Nz(DMax("RecordID", "TableName"),0) + 1
End If
End Sub

If this is a multi-user environment, you ought to move the code to the Form_BeforeUpdate event, which fires at the last moment, decreasing the chance of two users getting the same number.

 

[Pirie]

Where would I enter that code?

 

[Pirie]

Private Sub Form_Load()
If Me.NewRecord Then
   Me!RecordIDTextbox = Nz(DMax("RecordID", "TableName"),0) + 1
End If
End Sub

Thanks for that code but I get an error message when entering this code. My table is called property and the field is property ID. I take it that RecordIDTextbox should be replaced with the name of the textbox

Thanks

 

[missinglinq]

No, RecordId would be the actual name of the field in the table!

Private Sub Form_Load()
If Me.NewRecord Then
   Me!RecordIDTextbox = Nz(DMax("PropertyID", "Property"),0) + 1
End If
End Sub

If your field name has a space in it, i.e. Property ID, you'll need to use square brackets around it, which is why object names shouldn't have spaces.

Private Sub Form_Load()
If Me.NewRecord Then
   Me!RecordIDTextbox = Nz(DMax("[Property ID]", "Property"),0) + 1
End If
End Sub