Referencing Field Names

[andy-29]

I need to autofill a field on a table from a field on a form.
Table Name is Requests and Field Name to update is Status
The user will choose from a combo box and after update should populate the corresponding field on a table.

This is what I have (missing the table and field to update):


Private Sub Status_AfterUpdate()
If Not IsNull(Me.Status) Then
??????? = Me.Status
End If
End Sub

[JeroenMioch]

If your table is the recordsource for the form, this should be done without coding.
Just right click the combobox and select the control in the list. Second one from the top.

If the form is not the recordsource then and update SQL should do it.

Let me know

[andy-29]

The form is the record source. User clicks on the combo box, which then should update the corresponding record on the table.

How do I reference the field to be populated/updated?

Private Sub Status_AfterUpdate()
If Not IsNull(Me.Status) Then
[Requests.Status] = Me.Status
End If
End Sub

I'm getting an error, something about invalid field. What is the correct syntax to reference the field on the table?

[ssanfu]

I need to autofill a field on a table from a field on a form.

There are no "fields on a form".

Tables have fields.
Forms have controls.
Controls are bound or unbound.
If a control is bound, it is bound to a field in the form recordsource - the table names are not used (unless there are two or more tables in the query, each have a field with the same name). The recordsource can be a table or a query. If the control ("Status") is a bound control, the data is automatically saved when the record is saved when you move to a different record or close the form.

If the control is not bound, you must write code to save the data to the field in the correct record..



-----
"Me" is a shortcut to refer to the active form. If you have a form named "Main" and it is the active form, then to refer to a control named "MyStatus", you can use:
Me.MyStatus
or
Forms!Main.MyStatus
or
Forms!Main!MyStatus


If "Main is NOT the active form, you would use:
Forms!Main.MyStatus
or
Forms!Main!MyStatus

[andy-29]

The content of the combo box named status will update a field(named status also) in another related table(request).

The form which was created from a table named Marker, has a combo box named Status. What ever the user chooses on the combo box will also update a related table named Requests that has a field named also Status.

New with access, not sure if I'm explaining it right.

[ssanfu]

You should read the following two links (these will help in the future):

"The Ten Commandments of Access" http://access.mvps.org/access/tencommandments.htm
"The Evils of Look up FIELDS" http://access.mvps.org/access/lookupfields.htm

Lookup fields are bad, combo boxes are good
------


I would name the combo box "cboStatus" and the fields I would name "MarkerStatus" and "RequestsStatus".

The combo box on the form should be bound to the field "MarkerStatus".

To update the field "RequestsStatus", you have 3 options:
1) The recordsouce for the form that has the combo box "cboStatus" would include the table "Requests" (in a relationship).
2) A open form with the recordsource that includes the table "Requests" and control bound to the field "RequestsStatus".
3) Code to find and update the correct record in the table "Requests".

[andy-29]

Thanks Steve,

I redesigned the db and used option #2.