Combo Box not updating Text Box

[gerryp]

Total Access amateur here.

I have a bound Department Name combo box (Combo18) and unbound Dept. Cost Centre textbox (Text33).

When I choose a Department Name , I expect the Dept. Cost Centre textbox to update immediately, but of course it doesn't with no error, and I am lost ??

(combo18) Row Source:

SELECT DropDownListsTbl.Code, DropDownListsTbl.Group, DropDownListsTbl.Description FROM DropDownListsTbl WHERE (((DropDownListsTbl.Group)="DEPT")) ORDER BY DropDownListsTbl.Code;

(combo18) After Update Event:

Me.Text33 = Me.Combo18.Column(3)

 

[isladogs]

Combo boxes column numbering starts at 0 so yours are 0,1,2.
There is no column 3.

Also CostCentre isn't in your combo row source. Add it

Finally, you may need to requery the textbox in the AfterUpdate code for your combo box

 me.text33.requery

 

[bob fitz]

I'm guessing that you are trying to reference the third column of the combo box with:

Me.Text33 = Me.Combo18.Column(3)

but the column count starts with 0 so the third column would be referenced with:

Me.Text33 = Me.Combo18.Column([B][COLOR="Red"]2[/COLOR][/B])

 

[gerryp]

Combo boxes column numbering starts at 0 so yours are 0,1,2.
There is no column 3.

Also CostCentre isn't in your combo row source. Add it

Finally, you may need to requery the textbox in the AfterUpdate code for your combo box

 me.text33.requery

Many thanks Ridders for the pointers.

I've made the changes as you suggested, but unfortunately textbox33 is still blank.


Here's the rowsource of combo18, does it look ok?

SELECT DropDownListsTbl.Code, DropDownListsTbl.Group, DropDownListsTbl.Description FROM DropDownListsTbl WHERE (((DropDownListsTbl.Group)="DEPT")) ORDER BY DropDownListsTbl.Code;

When the rowsource query is run, it returns values for the 3 columns - see the attached

All credit to laziness : the Cost Centre textbox (text33) should display the contents of DropDownListsTbl.Description

 

[arnelgp]

On design view set your combo Column Count property to 3.

 

[gerryp]

On design view set your combo Column Count property to 3.

Done that.

When the combobox's dropdown arrow is clicked, the 3 columns appear but
textbox33 is still empty ??

 

[isladogs]

SELECT DropDownListsTbl.Code, DropDownListsTbl.Group, DropDownListsTbl.Description FROM DropDownListsTbl WHERE (((DropDownListsTbl.Group)="DEPT")) ORDER BY DropDownListsTbl.Code;

Your combo box code will filter all values where the Group value = 'Dept'.
Is that what you want? Does it give any records?

Arne's suggestion will allow you to see all the data in your combo box (just like if you ran the row source using the query design window) ...
BUT it won't solve your issue

You still need to have this in your After_Update code:

Private Sub Me.Combo18_AfterUpdate()
    Me.Text33 = Me.Combo18.Column([COLOR="Red"]2[/COLOR])
    Me.Text33.Requery
End Sub

 

[arnelgp]

Still no luck, try
Me.text33=me.combo18.column(2, me.combo18.listindex)

 

[gerryp]

Your combo box code will filter all values where the Group value = 'Dept'.
Is that what you want? Does it give any records?

Yes, it brings back records, filtering out departments -
in my attachment, on the right side, you'll see them.

 

[arnelgp]

Gi back to design view of your form. See if there are any textbox infront of textbox33

 

[gerryp]

You still need to have this in your After_Update code:

Private Sub Me.Combo18_AfterUpdate()
    Me.Text33 = Me.Combo18.Column([COLOR=red]2[/COLOR])
    Me.Text33.Requery
End Sub

[/QUOTE]

Ok, some progress has been made after entering the above.

textbox text33 now updates once you select a department from the combo box.

Hope now the following is understandable !! ;

When I add a new record, choose a department, their cost centre code appears - excellent.

I've 80 pre-existing records in my database.

When I go back to the 1st record, select IT Department, the cost centre textbox text33 displays the correct cost centre (RA30) - fabulous !!

But when I navigate to the 2nd record, the NICU department's cost centre is now the same as the IT Department, and so on ??

Should there be a re-query, on textbox text33 somewhere else or how else can I stop text33 from holding onto the previously chosen department's cost centre ??

 

[isladogs]

Your combo box code will filter all values where the Group value = 'Dept'.
Is that what you want? Does it give any records?

Yes, it brings back records, filtering out departments -
in my attachment, on the right side, you'll see them.

Do you mean the screenshot in post #4?

Also what type of form is it - is it a 'special type' like split or navigation form?

Suggest you do screenshot of the form in form view and design view?

 

[gerryp]

Gi back to design view of your form. See if there are any textbox infront of textbox33

no, no other text boxes in front of Text33

 

[gerryp]

Nothing attached

Look back to Post #4

 

[Minty]

You'll need to add

 Me.Text33.Requery

to the On_Current Event of the form. The On_Current event fires when you move between records.

 

[gerryp]

Do you mean the screenshot in post #4?

Correct

Also what type of form is it - is it a 'special type' like split or navigation form?

Form with Subform, Navigation

Suggest you do screenshot of the form in form view and design view?

Screenshots Attached

 

[gerryp]

You'll need to add

 Me.Text33.Requery

to the On_Current Event of the form. The On_Current event fires when you move between records.

Done the above, but the cost centre's are been overwritten when you navigate between records

 

[arnelgp]

you don't need a requery, what you need is a code on your Form's Current event:

Private Sub Form_Current()
On Error Resume Next
If Trim(Me.Combo18 & "") <> "" Then
Me.Text33 = DLookup("Description", "DropDownListsTbl", "Code = " & Me.Combo18)
Else
Me.Text33 = Null
End If
End Sub

 

[gerryp]

you don't need a requery, what you need is a code on your Form's Current event:

Private Sub Form_Current()
On Error Resume Next
If Trim(Me.Combo18 & "") <> "" Then
Me.Text33 = DLookup("Description", "DropDownListsTbl", "Code = " & Me.Combo18)
Else
Me.Text33 = Null
End If
End Sub

Afraid that hasn't worked

 

[isladogs]

Not sure I see the advantage of this approach BUT if you are using it THEN

You still need to reference column(2) in that code you posted.
Also it's a string so you need text delimiters

e.g. try this ....!

If Nz(Me.Combo18.Column(2),"") <> "" Then
Me.Text33 = DLookup("Description", "DropDownListsTbl", "Code = [COLOR="Red"]'" & Me.Combo18.Column(2) & "'"[/COLOR])
Else
Me.Text33 = Null
End If

BTW - looking at the image in post 4 - the code for RCSI is in "" marks

And I hate to tell you this but navigation forms are an absolute pain to work with. Many of us here avoid them like a bad smell...

You may be approaching the stage where posting a trimmed down version of your db would speed things up