Null Values on left side of = sign

[foxxrunning]

I have a combo box on a form which displays Id, Last and First names. I want the CBO to save the Id and fill in the Last and First names in their respective fields on the form.
This is the code placed in the On Change event of the CBO.

Private Sub cboPatient_Number_Change()
Me.txtLastName.Value = Me.cboPatient_Number.Column(1)
Me.txtFirstName.Value = Me.cboPatient_Number.Column(2)
End Sub
When I hover my mouse over the txt.LastName.Value or over the txt.FirstName.value I get a NULL value for these. When I hover my mouse over the Me.CboPatient_Number.column(1) or (2) I see the appropriate values as chosen by the CBO Patient_Number.
Does anyone have any idea why these values are not properly transferred to the left side of the equations?? Thanks for any help.

 

[theDBguy]

Hi. Not sure, but try moving your code from the Change to the AfterUpdate event and see if that makes any difference.

 

[foxxrunning]

Will try it. Thanks.

 

[foxxrunning]

Nope, Same Null values in both.

 

[theDBguy]

Nope, Same Null values in both.

Okay. You said you're hovering the mouse over the code, but what is actually happening on the form? Are you seeing the changes there?

 

[foxxrunning]

Both of the values are blank on the form. Nothing is displayed on either Lastname or Firstname.

 

[Deleted member 147267]

If this
Me.txtLastName.Value = Me.cboPatient_Number.Column(1)
debug.print Me.txtLastName
Me.txtFirstName.Value = Me.cboPatient_Number.Column(2)
debug.print Me.txtFirstName

doesn't show you anything in the immediate window when the sub has completed, then have you turned off warnings or do you have error handling code that you haven't shown us?
EDIT - also, watch the status bar for messages.

 

[foxxrunning]

That is the only code I have. Did not include any error handling code (would not know how to write it) Will see what the debug line shows and report back. Thanks.

 

[foxxrunning]

Can I paste the code into the immediate window and step through it there? Tried that and got a "You cant assign a value to this object" Runtime Error 2448

 

[MajP]

If you pasted it, did you change Me. to Forms("yourFormName").?

 

[Deleted member 147267]

Can I paste the code into the immediate window and step through it there?

No need. Your code is on an afterupdate event? Just do the update then go look at the immediate window. Would be a good idea to clear whatever might be there first.

 

[foxxrunning]

I just stepped through the code in the debugger and got the "unable to assign a value to this object" there. Didn't paste it into the immediate window. Thanks.

 

[Deleted member 147267]

There must be something about the control or form properties that are preventing updates.

Is the form Allow Additions, or Allow Edits set to No?
Do the controls have expressions (formula) in their recordsource property?
Is the form based on a query that is not updatable (and these controls are bound to fields in that query)?
Do either of the controls or the table fields have input masks (a bit of a stretch but worth asking).

 

[foxxrunning]

Form Allow additions - Yes
Form Allow edits - Yes
I have tried the control source for each of the fields in both these ways Control Source Lastname Or Control Source =[Lastname]
Form is not base on a query Just a table (Transactions)
No input masks.
Do not see a Recordsource property. Just a Control Source property. Am I missing something? This is Access 2016.

 

[Deleted member 147267]

Sorry, I meant control source. Only thing I can think of at the moment us possibly a corrupt control assuming you picked LastName from the dropdown in the property sheet (no = sign). Before replacing it I'd make sure I could edit the table field directly in case there's a setting there that's the cause, or it's the child side of a relationship.

 

[CJ_London]

I presume you have set the column count for your combo to 3 (or more)

also you should not need any code, just set the contolsource to your txt control to

=cbopatientnumber.column(1)

 

[Deleted member 147267]

I'm confused. How is

=cbopatientnumber.column(1)

going to solve

I just stepped through the code in the debugger and got the "unable to assign a value to this object"

That's what I've been basing my latest answers on. Maybe I'm on the wrong path...

 

[CJ_London]

It solves it because you do not need any code at all

 

[foxxrunning]

Maybe I should have been asking "How do you populate more than one field from a combo box on a form". I had been using the combobox to display 3 fields, only one of which is stored in the table after choosing it via the down arrow on the CBO. The other two fields I had wanted to store in their respective fields on the form via the code Me.txtLastName.Value = Me.cboPatient_Number.Column(1) and Me.txtFirstName.Value = Me.cboPatient_Number.Column(2). Is there a know way to do this or is there another way which might actually work. Thanks for any help.

 

[theDBguy]

Maybe I should have been asking "How do you populate more than one field from a combo box on a form". I had been using the combobox to display 3 fields, only one of which is stored in the table after choosing it via the down arrow on the CBO. The other two fields I had wanted to store in their respective fields on the form via the code Me.txtLastName.Value = Me.cboPatient_Number.Column(1) and Me.txtFirstName.Value = Me.cboPatient_Number.Column(2). Is there a know way to do this or is there another way which might actually work. Thanks for any help.

Hi. Sure, you could ask that question. But, the real answer is: "You shouldn't really do that." What I mean is, you don't want or need to store the same information in multiple tables (normally). You can "display" the other information on your form, which is what @CJ_London was trying to say, by using expressions or functions.