Redisplaying refreshed combobox

[jancliff]

I have a subform on a main form. On the subform I have two fields, ClientID and AdHocID (combo box). The user can only choose an AdHocId that is related to ClientId (depends what they choose in the field ClientID).

This works lovely if I set the first Column Widths to 1 on the field AdHocID(then displayes the AdHocID number). I get into problems if I set the width properties to 0 so that it will display the AdHocName (which makes more sense to the user on the form)

- If I add another record to the subform, the data disappears in the field AdHocID or it appears to move up beyond the subform...

Can any one point me in the right direction please?

Many Thanks

 

[maxmangion]

you need to Requery.

put Me.Requery in the afterupdate event of your combo box.

 

[jancliff]

Thanks

Thank you for the reponse. I am still a little confused why the combo box worked perfectly ok if I only displayed the ID field but as soon as I wanted the description it did not work. Would it have needed the requery also?

 

[maxmangion]

did you set the column count to 2, and then the column widths 0cm; 2.54cm (or your prefered width) ?

 

[jancliff]

Yes I did.

I have added the refresh but it still takes out the data I put in if I choose to add another record in the subform. If I set the properties to 1, 2.54 it works perfectly ok but returns the ID field of the AdHocID field (a number is not very explanatory to the user unfortunately but I need the bound column 1 to be stored in the database). I have looked at other posts to see if I could perhaps have another field that then displays the value of AdHocID next to it but I am getting a little confused and get errors #name?

Many thanks for your help it really is appreciated I know us beginners probably get on your nerves with silly questions... Thanks again

 

[bruced3846]

I am having a similar problem - just posted a new thread, "Cacading Combos in DataSheet view". The problem with cascading combo boxes tied to a query (at least as far as I understand it) is that there is only one query for the form, so when you select a different ClientID, the subset (query results) of available AdHocID's changes, so the value displayed in the previous record is no longer valid and blanks out.

"- If I add another record to the subform, the data disappears in the field AdHocID or it appears to move up beyond the subform..."


Microsoft has an article on this, but the resoultion is useless for datasheet views. Still looking for help, will keep this thread updated too...

Bruce Davis

 

[maxmangion]

is it possible that you will upload a sample of your form in question ?

 

[jancliff]

No worries. The form I am having problems with is frmTimeCard and the subform within it frmTimeCardHours - You can see by looking at the field AdHocId on the subform to see what I mean - I have attached it with it not working. If you change the width properties back to 1cm;2.54 it works lovely.

Many thanks

Also Thanks to you too -Im glad Im not the only one experiencing a problem. There must be a way around it.

 

[bruced3846]

Not sure there is a way around it. Been looking for two weeks, tried every event and still no luck. I may have to abandon normalization and store duplicte data in my details table - if I can even get around it that way. Because I want to use a combo box to display the possible choices, the fact that "Microsoft Access maintains only one query recordset for a combo box rather than one recordset for each combo box in each record. " according to http://support.microsoft.com/default.aspx?scid=kb;en-us;208866 means that every time you choose a different record the query resets.

Bruce

 

[jancliff]

What I fail to understand is it works if I only want to display the ID field.

However to even get this to work, I had to work around it by the following;
Firstly I altered my query so that it used a public (aka global) variable for the contract. I created a module containing a public variable i.e.

Public strContractId As String

unfortunately you can't reference this public variable in the query (don't know
why) so I created a function that will return the variable i.e.

Function getContractId()
getContractId = strContractId
End Function

This also needs to go into the module.

I then replaced the existing 'Criteria' in my query with the function i.e. getContractId()

Then I set the public variable and requery the drop down list everytime some enters the AdHoc cell by doing the following event into your subform,

Private Sub AdHocWork_GotFocus()
strContractId = Me.ContractID.Value
AdHocWork.Requery
End Sub

Does this make sense? If you look at my upload it may help. Its probably very long winded!

 

[bruced3846]

In the microsoft article is says the resolution is to not hide the bound column, so when you only want to display the id field you are doing just that. The problem I have is that normalization procedures call for a unique id on each record, and if I do that then I have to hide to bound column or it makes absolutly no sense to the user. Make sense?

Bruce

 

[jancliff]

Totally. I glad Im not the only one. I have been trying to resolve this for about 3 days now and my brain hurts.... Good luck If I find out anything else I will let you know....
Thanks

 

[bruced3846]

No fix for cascading combos?

Well, what I finally ended up doing was creating an extended detail form fpr my data entry, and calling it from a command button on the form. This way the cascading combos are in a single form, so the single query is fine, and the values stored in the table show up fine on a datasheet as long as you are not using the combos in the datasheet view.

Bruce