Refresh controls populated from combo box

[davidbodhi]

I have a form where the user selects a client from a combo box.

Doing so populates 4 controls with info about the client, using the Exit function of the cbo box. The Record Source of the form in question does not store this info; it comes from a different table.

This works fine.

When the record has been saved and I come back to it, however, those four controls do not display the client's information, since the combo box hasn't been exited.

What's the best way to get the form to repopulate those controls without having to reselect the combo entry?

Thanks a lot for the help!

 

[quiksilver1804]

I'm not sure why the data goes away. Do you close the form and go back to it? If you are hitting a save button to save the data, then i think the best way to pull the information is doing a Dlookup(). If you are unfamiliar with it, go here for syntax:

http://www.mvps.org/access/general/gen0018.htm

The format is basically: Dlookup("Field", "table", "where clause")

Your where clause would be where cmbClient = the client field in the table. Hope this makes some sense...

 

[RV]

those four controls do not display the client's information, since the combo box hasn't been exited

Is your combo box set up as a bound control?
Bound as in bound to the underlying record source of your form?

RV

 

[davidbodhi]

RV - Yes, the combo is a bound control, but the four others are not, since the information's coming from a different table than my record source. AfterUpdate of the combo just places it in the form.
Quicksilver's answer sounds like it should work.

Quick, this happens either when I scroll to another record or close and reopen the form. I'm not storing these values in the record source, since they're stored elsewhere. I'm sure that's why the controls are empty when I come back to the form.

Where would that Dlookup code most appropriately go?

 

[Pat Hartman]

The BEST solution is to use a query that joins the main table to the lookup table. With that method, NO code is required in the AfterUpdate event of the combo. However, when using this method, it is best to set the Locked property to Yes for all lookup fields since you don't want them to be accidentially updated.

DLookup() is inefficient and is to be avoided at all costs.

 

[davidbodhi]

Pat - Can you clarify?

Do you mean use a query for the combo box that joins the two tables, or in the Control Source of the text boxes?

If the former, I think that's what I'm doing. Maybe I'm just not doing 'enough'...

If the latter, would that look at the selected name in the combo box and then pull the related fields from the other table and place the info?

Would I need to allow for a null combo box, so a new record doesn't show #error in those text boxes?

Or are you talking about code in the form itself, that runs every time a record is displayed?

Too many options for a rank beginner to know what you mean....

 

[RV]

What Pat likely means is to use the query she suggests as the source of your form.

RV

 

[Pat Hartman]

Use the query as the RecordSource for the form. When you choose a value from the combo, Access will AUTOMATICALLY populate the fields that you included from the lookup table. You don't need any code to make this happen. All you need is the query as the form's RecordSource.

 

[davidbodhi]

OK... and the fact that I'm using a query for this won't impact the addition of new records, then?

Seems to me I've read a number of comments on the forum about using queries so data in tables doesn't get inadvertently altered.

 

[Banana]

That's because not all queries are updatable. You would want to search about how to make queries updatable. Understand how Join will help your query, and in case where you cannot update query at all (usually when you have two unrelated things related to single common thing and you need all three at once), the best thing to do is to use a subform instead.

 

[davidbodhi]

OK, I am getting hints... So far, creating a query using the wizard, I can either leave it a SELECT and make it the RecordSource, or make it Updatable and it won't let me make it the RecordSource.

I gather that mostly means I need to know more of what I'm doing.

Pat, a lot of the controls on the form are calculated controls and therefor unstored. Presumably, you'd then put the calculations into the query?

 

[Banana]

If you tried to store calculated values in a query, it would render the query nonupdatable, as changing a value would impact the whole query.

Instead, have your query return all values youuse to do calculating, then do the calculating on the form itself, by a unbound textbox or something like that.

 

[davidbodhi]

Thanks, Banana....

Like many rank novices, I've leapt over the most basic things, done some stuff that works, but find myself at a loss over the simplest things.

I can create a query that pulls the data I want and make it the RecordSource for my form. This query, built with the wizard, is not updatable. If I make it updatable, it's no longer available in the list of potential RecordSources. If I update it after it's the RecordSource, I get errors when I try to do anything at all.

Looking back, that's likely why I ended up using my tables instead of queries as RecordSource in the first place.

I confess it also doesn't makes sense to me that an updatable query protects data more than just using a table.

After spending hours reading messages on this forum, reading help screens, looking at databases so much more complicated than mine that I can barely tell where (or if) they're relevant to my current problem, I'm at the point where I'd just like to know how to make the dang form perform the calculations, when looking at an existing record, as it does with a new record.

New records do just what I want. (By the way, all my problems are with calculated controls, not stored data from fields.) Existing records, I have to click on certain controls to invoke the calcs. That's doable, but no user wants to take that trouble. Placing the same calculation codes elsewhere, in an attempt to get them to run when I pull up an old record not only doesn't work, but subtly corrupts the database till it calculates things incorrectly and I have to delete it and go back to another copy of a functional one. (I learned long before working with databases to work on a copy, only.)

So, it's frustrating.

For example, I have the following event procedure on Exit of control Alternate Fee:
If Me!txtCoinsurance > 0 Then
If Not IsNull(Me!txtAlternate_Fee) Then
Me!txtTherapy_Total = Me!txtAlternate_Fee
Else
Me!txtTherapy_Total = (Me!txtTherapy_SubTotal * (Me!txtCoinsurance / 100)) + Nz(Me!txtCopay, 0)
End If
Else
If Not IsNull(Me!txtAlternate_Fee) Then
Me!txtTherapy_Total = Me!txtAlternate_Fee
Else
Me!txtTherapy_Total = Me!txtTherapy_SubTotal + Nz(Me!txtCopay, 0)
End If
End If

This works fine. It checks to see if a coinsurance exists, if an alternate fee exists and if a copay exists and then comes up with the amount an insured patient actually owes for the visit.

If I put that same code into OnGotFocus of the control that gets the focus when a record is displayed, it does nothing. And that is not because of controls being empty, I have it check the contents of another control that has an autonumber if it's an existing record before it runs.
If I define that same code as a Function and call it, instead of writing it out (in the 2 places where it needs to be) it works for a while and then starts giving incorrect amounts. I have no idea why. Deleting all records and performing a Repair and Compact doesn't fix it. Time to delete and start with a new copy.

<whine whine>

So, if you or anyone can either suggest place to run calcs (like the one above) when an old record is pulled up OR give simple generic steps on how to use a query as RecordSource and still be able to create new records, I'd appreciate it, to say the least.

 

[Banana]

!) If you make a query and want to see it in list of recordsource available, you need to save the query first (i.e. save it as you would save a word document). Building it and exiting it will simply put a SQL statement in the recordsource, which is also okay, though I personally prefer to save the query so I can go back and see what I have my form bound to and change it if necessary.

2) OnGotFocus isn't right event; I would put it in AfterUpdate event of the last control you need to fill in data to be calculated (but that will not work if your user decided to input the entry backward or out of order), or have a button to execute the button (which will alway do the trick, but require one extra mircosecond from your user to execute).

 

[davidbodhi]

Thanks, Banana....

I've apparently resolved my problem through a combination of using a query and blasting past a blind spot in my mind regarding correct syntax to use in a Control Source.

If I don't come whining back, then I'm squared away.

 

[Banana]

If you edit the query from form design view, and didn't save it, it'll be saved as a SQL statement which would be different from your old query. To keep using your query's name, you simply need to save it.

2) You want the code in OnCurrent event of the form among with the Afterupdate.

Just FYI:

Whether I have two events or more where I need to run same procerhures, I can write something like this

Private Sub control_AfterUpdate...

Call ReCaluclator

End sub

Private Sub OnCurrent_Form...

Call ReCalculator

End Sub

Private Sub ReCalculator()

your code here

End Sub

This way you keep your code in one place and make your debugging easier.