Populate text boxes from combo-error msg

[TopherSD]

I am trying to have a user update several fields when a particular record is chosen. I set up a multi-column combo box for the record selection and it populates the other fields just fine when there is data in them. My problem is that if the field is blank I get a runtime error "cannot be zero length".

Also, the combo box shows all the columns associated with it when selecting the record. Is there a way to have it only display the record (DCN)?



I am using the following code:
Private Sub cboDCN_AfterUpdate()
Me.LookupIPA = Me![cboDCN].Column(1)
Me.CorrectMbrID = Me![cboDCN].Column(2)
Me.CorrectHPC = Me![cboDCN].Column(3)
Me.Action = Me![cboDCN].Column(4)
Me.RCode = Me![cboDCN].Column(5)
Me.RDate = Me![cboDCN].Column(6)
End Sub

 

[DCrake]

Go tot the properties of the combo box and set the colwidths to 0 for the columns you do not want to show. Regards you zero lenght error this is generated from the settings in your table. Have a look at all the text type fields and see which one(s) have Allow Zero Lenght set to No and change them to Yes.

David

 

[TopherSD]

Thanx David. That worked for those two issues.

I am now seeing another problem. When a record is selected and the fields updated by the user, Access is updating only the first record in the query and not the record that was selected. How do I fix that?

 

[DCrake]

How are you performing the update of the record? and at what point are you doing it?

 

[TopherSD]

The update is triggered by a save record button that the user will press after they have filled out the information.

 

[TopherSD]

Any help on this would be appreciated. I still cannot get this form to work properly. I am trying to have the combo box pull a specific claim record and it's related fields, then be able to change those fields. Every time I save it though it overrides only the first record and not the recording corresponding to.

I have attached a sample of the database . Thanx all!

 

[missinglinq]

You have found out, as many people have, to their chagrin, that Access often does exactly what you tell it to do! Your problem is that you're never leaving your first record!
While sitting on your first record, you're making a selction from the combobox with this AfterUpdate code:

Private Sub cboDCN_AfterUpdate()
Me.LookupIPA = Me![cboDCN].Column(1)
Me.CorrectMbrID = Me![cboDCN].Column(2)
Me.CorrectHPC = Me![cboDCN].Column(3)
Me.Action = Me![cboDCN].Column(4)
Me.RCode = Me![cboDCN].Column(5)
Me.RDate = Me![cboDCN].Column(6)
End Sub

And Access is assigning all of those values from the combobox to the fields in the current record, which is the first record! That's because the code you have doesn't retrieve a record! It only assigns the values to a record, in this case the first record!

Delete your current combobox and create a new one, like this:

1. Add a combo box to your form.
2. The Combobox Wizard will pop up
3. Select "Find a record based on the value I selected in my combobox."
4. From the table or query the form is based on, click on the field you're searching by (I believe it's named DCN here) to move it to the right side.
5. Hit Next.
6. Size the column appropriately.
7. Hit Next.
8. Name the combobox.
9. 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. You can then edit the fields that need to be edited and hit the "Save" button.

 

[TopherSD]

Why can't Access do what I want it to do and not as I code it to!

Thanx! That of course worked like a charm.