Solved Prevent combobox overwriting existing values. (1 Viewer)

[HillTJ]

Hi, i have a bound combobox on a form that gets its data from a query and displays the record as continuous on a subform. My intention is to populate the combobox with only the latest 'revision status' of a record. However, what happens is that clearly an existing record may be superceeded and no longer present in the query so is not shown on the form. I'm thinking of creating an unbound combobox to capture the 'latest' lookup data then using the 'onchange' event of the unbound combo to check whether populating the bound field is empty or not. If empty it copies the value from the unbound combo across. Is this a reasonable approach?. Appreciate any assistance or even a code example. Thanking you in advance.

 

[theDBguy]

Hi. It's hard to say without seeing your form. Can you post a screenshot?

 

[bastanu]

I would use a different approach: populate the bound combo box with a table/query that includes all possible choices (so all records have a "status") then in the Enter event of the combo you switch its rowsource to a "restricted" one to only allow valid choices as of its current status then in the Exit event you reset it to the more inclusive one.

Cheers,
Vlad

 

[arnelgp]

I think it will "blank out" fields who's values are not in the rowsource of the combo when you "enter" the control.

but there is a hack, somewhat similar to your first post.

 

[HillTJ]

Arnelgp, you are correct. I'll post a screenshot of the form in design view so you can see the layout.

 

[HillTJ]

Guys, have a look @ the screenshot attached. Hope it helps.

 

[bastanu]

Sorry, a bit lost here, what exactly are you showing here? Have you tried what I suggested? You said it is a continuous form that is "blanking out" fields, can you please show us that? The best would be for you to post a sample db illustrating your issue with just a couple of dummy records.

Cheers,
Vlad

 

[HillTJ]

Bastanu, i'll try to explain again, be patient!. I have a form with a linked subform. The subform displays training courses that are assigned to the job (main form). This works fine. Now to assign a new course i use a bound combobox, which upon selection populates the other fields of the subform. No problems. However, the combo box gets its data from a query that populates with only the latest revision (uses max to return only the latest revision of the document). This works too,but it clearly will not populate a 'superseded ' revision selected in an earlier record (its a continuous form) as that record is no longer present in the query. I was thinking of using an unbound combobox to select the document and only populate the other fields if they contained a 'null' that way it would not overwrite the existing field with a 'null' . Arnelgp has hit the nail on the head with his post #5 and alluded to a work around? I am sure your technique would work but you're moving a bit beyond my understanding. I'd like to get this concept into my head as it'll reoccur similarly with other forms. Appreciate a bit more tuition.

 

[arnelgp]

there is always a penalty for the hack, I want you to discover it.
under the cboTraining, there is a Textbox, same height, almost same width
same top and left of combo.
see the controlsource of textbox
see other events on the subform.

 

[HillTJ]

Arnelgp, thanks once again. Will give it a go when i get some time & let you know.

 

[Uncle Gizmo]

However, the combo box gets its data from a query that populates with only the latest revision

I usually tackle this problem with a pop-up form. See attached..

Here's a very nice example of the technique which I did in answer to another question a couple of a couple of years ago.

12) - Checklist With Multiple Choice - Nifty Access

 

[bastanu]

Please have a look at the attached, I used Arnel's example.
Cheers,
Vald

 

[HillTJ]

Vlad, Yes. You're example is exactly what I need.I added a couple of revisions to 'Loto' and they were properly replicated in the combo box without removing records of earlier versions. Problem is I don't really understand how you achieved this & don't understand the relationships that start with Msys.......... Also how does access prioritise revisions that are not numbers? If you have the time, Id really like to forward a copy of my database so you can see what I've done. Really appreciate the support from all forum members who contributed. Really encouraging.

 

[bastanu]

Sure, you'll find my contact email on my site in my signature, please zip it and remove any confidential info. I am not sure what you mean by "relationships that start with msys", basically the way it works is that originally the combo must be based on the "most inclusive" record set which usually is the table (or a query based on it with no criteria if you need to apply certain custom sorting). That way when you open the form all records are showing. Now when you edit or add a new record you want to force the users to select the latest revision; you do that by switching the row source of the combo in its Enter event (in the example that was the qryLatest.....). And finally after the user picks the appropriate entry you reset the row source of the combo back to its original "most inclusive" one so all the older records show up.

Cheers,
Vlad

 

[HillTJ]

Vlad, Appreciate it works a treat. When I look at the relationships under the relationships tab, I see 3 linked tables called 'MsysNavPaneGroup...'. I don't see those tables in my form?.

 

[bastanu]

Glad to hear it works for you!. Rest assure those relationships have nothing to do with how it works, they are probably defaults (msys is the prefix for the Access system tables and usually they are not visible unless you switch on "Show System Objects" under the Options/Current Database/Navigation).
Cheers,

 

[arnelgp]

did it really worked?
I click on Each record's combo and it is "blanking" the other combos.
this is not what I proposed.

 

[deletedT]

did it really worked?
I click on Each record's combo and it is "blanking" the other combos.
this is not what I proposed.

Yes, It was the same here.
But thought if OP is satisfied no need to mention it.

 

[bastanu]

@arnelgp and Tera, would you please explain (maybe with some screen shots ) what do you mean by "blanking" the other combos. I thought there was only one combo in your example arnelgp and that is what I was trying to do. Sorry if I misunderstood the issue....

Cheers,

 

[deletedT]

@arnelgp and Tera, would you please explain (maybe with some screen shots ) what do you mean by "blanking" the other combos. I thought there was only one combo in your example arnelgp and that is what I was trying to do. Sorry if I misunderstood the issue....

Cheers,

Simply open the file and try to add new records. The first two records training changes to blank.