Display Record in form based on variable

[JTM40000]

Hi Everyone,



I'm trying to display different elements from a table onto a form that displays those values from one specific record onto that form. There's three fields:

CPO
Event ID
SO #

I want the user to bring up the specific CPO on the combo box and then display the other two fields in protected text boxes in the form in the same area.

I'm having an issue ID'ing what to use for control source for each text box based on the value of the CPO field.

Any help?

[June7]

Simplest approach is to include the other two fields as columns in combobox RowSource. Reference those columns by their index. Index begins with 0. If value is in column 2 its index is 1. Expression in textbox:

=comboboxname.Column(1)

[JTM40000]

Simplest approach is to include the other two fields as columns in combobox RowSource. Reference those columns by their index. Index begins with 0. If value is in column 2 its index is 1. Expression in textbox:

=comboboxname.Column(1)

Thank you!

This did what I was looking for here. My next step here is to be able to update only the SO # field based on the values of the other fields which can be locked. The default at the moment appears to only be allowing edits to the CPO combo box field. The SO # field will be the one that users would have to input here.

[June7]

I am not understanding requirement.

Could you provide db for analysis? Follow instructions at bottom of my post.

Strongly advise not to use spaces nor punctuation/special characters in naming convention.

[JTM40000]

Hello,

Basically I want to be able to make the SO Number text box updatable and reflect that update in the related table. The CPO combo box would just bring up the appropriate record as a reference but no data would be updated on the other two boxes.

[June7]

Either you need to not have an expression in SO textbox ControlSource or you need another textbox bound to field.

Your data structure is not clear to me and I am still confused about what you want to accomplish.

[ArviLaanemets]

Create a single form (Single is type of form which displays a single record from form's source), with textbox controls for CPO and SO (Btw., it's a bad idea to have special characters - # in current case - in field names), and probably a combo box control for EventID (user sees Event name/description, but combo is linked to EvetID). Probably you don't want to edit CPO at all, so you can hide the control linked with it.

To this form, add an unbound combo box, where user can select CPO, and matching record is activated (this is done by AfterUpdate event of this unbound combo). Then user can change event or/and SO, and the changes are saved, when user moves to next record or activates saving from menu or by using keyboard shortcut. You also can add a command button which saves the record, when those options aren't good enough for you.

[JTM40000]

Create a single form (Single is type of form which displays a single record from form's source), with textbox controls for CPO and SO (Btw., it's a bad idea to have special characters - # in current case - in field names), and probably a combo box control for EventID (user sees Event name/description, but combo is linked to EvetID). Probably you don't want to edit CPO at all, so you can hide the control linked with it.

To this form, add an unbound combo box, where user can select CPO, and matching record is activated (this is done by AfterUpdate event of this unbound combo). Then user can change event or/and SO, and the changes are saved, when user moves to next record or activates saving from menu or by using keyboard shortcut. You also can add a command button which saves the record, when those options aren't good enough for you.

So here, are you saying to create a control such as a text box and hide that control within the form so that the other controls will display the desired output?

I'm still playing around with some of the form and control properties here to make this updatable through one form. Will advise on progress where warranted.

[ArviLaanemets]

An example of AfterUpdate event for record selection combo box (cbbSelectDevice):

Private Sub cbbSelectDevice_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object


    Set rs = Me.Recordset.Clone
    rs.FindFirst "[DeviceID] = '" & Me![cbbSelectDevice] & "'" --/ The syntax for this row in case DeviceID is text (as I have it)
--    rs.FindFirst "[DeviceID] = " & Me![cbbSelectDevice] --/ The syntax for this row in case DeviceID is numeric (the row is commented out - I added it to demonstrate the difference)
    Me.Bookmark = rs.Bookmark
End Sub