Combo box not updating subform (1 Viewer)

[Niniel]

Hello,

This is very strange - one of my combo box-subform combinations isn't working right. When I initially select a name in the box the correct phone number shows up in the linked subform, but when I change the name, the number remains the same until I either navigate away and then back to the record, or close the form and reopen it.
I don't think I did anything different with this one, so I'm at a complete loss as to why this would be happening.

Any ideas anybody?

Thank you.

 

[boblarson]

What code do you have on the AfterUpdate event of the combo box and what is the subform CONTAINER name on the main form?

 

[Niniel]

I don't have any code. This is just supposed to be a simple case of "select name, get phone number as well".

The subform is called sfrmRepresentatives.

I noticed that the ID that the subform is supposed to be linked on to the main form does not change when I select a different name in the combo box.
And when I went to check the link properties and clicked on them to get the link dialog, I got an error: The expression you entered refers to an object that is closed or doesn't exist.

 

[boblarson]

So, the main form has a record source that is based on this ID and the main form and subform are linked by this ID?

 

[Niniel]

Yes, in the main table, there's a field called RepID. The combo box has that as its control source, and uses a query on the Rep child table [RepID, RepName, RepPhone] to pull the actual names. The same table contains the phone numbers, and the subform just shows those. The subform was linked to the main form via RepID when I created it, but now that link seems to have been broken for unknown reasons.

 

[boblarson]

I think I just spotted the problem. You really don't need a subform if the table is the same. You want a subform if you want to display one-one or one-many linking together different tables. As far as I know you can't have the same table open twice on the same form.

 

[Niniel]

I'm not quite sure I understand your comment... Why would I have the same table open twice?
I'm doing the exact same thing with other child tables, and there's it's working.

 

[GaryPanic]

try after update .requery

g

 

[Niniel]

Thank you, that works.

Not sure why this was an issue to begin with though.
Oh well, I'm just glad it's taken care of.

 

[GaryPanic]

Combo boxs night mare -if you look at the samples section this is raised
what you need to do is look at your source on the combo then a couple of lines down in properties is after update

first type in re.query
see if that helps

if this does leave it in and go back to the source and change this qry and get the date from your table via a qry rather than a table (slightly confusing ) as these will be the same now your combo box looks at the qry instead of the table - great (I might bruble on here)

now the fields you want to populate write them down
these will be the data fields in your qry
your combo box will now be larger than you need limit the size so you end up with row source which should have all your field in it and column count should now be the number of field but column widths change to 0cm;3.572cm etc so you end up with the one you want

now we get to a bit of coding

your combobox will have a name combobox114 or whatever

now in afterupdate their is ... select this and go to code

you now need to type in

field1(or your name for the field you want to be populated) = combobox.column(0) ** Access starts counting from 0
this will populate the first data in the column count so let assume your qry has this
name
title
email
tel
fax
if you want the data on your form to have this the field you want populated
frmname=combobox.column(0) will equal name
frmtitel=combobox.column(1) = title

where I have used frm... replace with your field name

now this is where re.query kicks in
with out this you would get a one time population of data .
with the re.qry you change the combo bocx and the data should change all the way through
put a save option after the re.qry

i might be teaching you to suck eggs - but this should work - i am typing this without access being too open

 

[Niniel]

Thank you.

That was slightly more than I needed to know at the moment, but it sure was interesting.