I'm a little green at ACCESS so be gentle.

I have a combo box called "RERUN" that prompts for and retrieves a registration number(primary key)from the history of a table using a query. This query consists of the registration number which is stored in the Combo Box field and displays the Comment associated with that number.

Is there a way that when I satisfy the prompt criteria, to store the registration number in the "RERUN" field and the associated Comments in a field below? Also, if the "RERUN" field is left blank\null I would still like the use the Comment field to be part of the new record I am creating.

Thanks for any help.

You might like to try this:

set the 'BoundColumn' for the combo box to 1
set the 'ColumnCount' to 2
Set the 'ColumnWidths' to 0;4

and now (assuming the Registration number and comments are columns 1 and 2 respectively in the query underlying the combo), the combo box will SHOW the associated comment, but the VALUE STORED will be the registration number.

alternatively you could set the BoundColumn to 2, the ColumnCount to 2, The ColumnWidths to 4;0 (then the opposite of the above will happen (i.e. Ref will show, but comment will be stored), then add the code:

Private Sub TheComboBox_Change()
Me.TheTextBox.Value = Me.TheComboBox.Value
End Sub

which will put the corresponding (invisible) comment into the text box when the combo box changes.

Mike

Mike

Thanks for your help Mike but it didn't do the trick,but that's probably because I wasn't clear enough or I did something wrong.

To try and clarify what I am looking to do:

I open up a new record and it looks like this going down the form:

Registration No.
Field 2
Field 3
....
ReRun(Combo Box)
Comments

ADD CANCEL UPDATE FIND CLOSE <====Buttons

The Registration No. is the primary key for this record. The Rerun field looks up past Registration-No's and through a query displays a past Registration-No by prompting the user for it and also it displays the appropriate Comment for that Past Registration-No. It stores the past Registration-No. I have the Comment field of the current record as a Combo Box, but I believe it could be a field box from the table the form is based upon, or a Text Box, I'm not sure which one is the best to have for a proper solution.

What I'm looking for is when I'm in a New Record, I enter in the appropriate fields then when I come to the ReRun field, if I bypass this field I am able to enter an appropriate Comment field for this New Record.

But I would also like the option of when I come to the ReRun field I would like to prompt for a previous Registration-No and store that number in the ReRun field and the corresponding Comment of that prompted Record return and be stored in the Comment field of the Current Record.

When I used what you had suggested before, it prompted when I opened the form for the first time and it displayed and stored only the comment for the Registration-No I prompted for.

Any other help will be apprecitated. PS the query underlying the ReRun field is Registration-No in the first column and Comments in the Second.