Combo Selection Populating a Text Box (1 Viewer)

[mab9]

Probably an easy one here...I have a series of cascading combo boxes where once the last once it chosen, I'd like some sales info to populate in a group of text boxes. What command would I use for this?

Also, I have a checkbox that I'd like to use to either enable or disable a textbox as well. Any ideas?

Thanks!

 

[pbaldy]

1) in the after update event of the last combo:

Me.TextboxName = <insert what you want here>

2) In the after update event of the checkbox (the long version):

If Me.CheckBoxName = True Then
  Me.TextBoxName.Enabled = True
Else
  Me.TextBoxName.Enabled = False
End If

The short version:

Me.TextBoxName.Enabled = Me.CheckBoxName

 

[mab9]

Thanks Paul. I've got the enable/disable working now. Regarding the text box population, it would be feeding off of a query that pulls on the combo box selections. Using the after update event, it didn't seem to like that much.

=[txt_h_sales]=qry_sales_by_family_w_analysis!SumOfSales_Dollars

 

[pbaldy]

You can't refer directly to a query field unless it's the source of the form. You'll either need to open a recordset or use a domain aggregate function (probably DLookup in your case).

 

[mab9]

I had a feeling it was going to come to the DLOOKUP. I've been fighting with it a little. Based on the criteria from http://www.mvps.org/access/general/gen0018.htm, I put in...

=DLookUp(qry_sales_by_family_w_analysis![R12 Sales $],[qry_sales_by_family_w_analysis],qry_sales_by_family_w_analysis![PM Code]=[cboPM_Code] And qry_sales_by_family_w_analysis![Family Name]=[cboPM_Family])

When that runs, I'm getting an automation error or some sorts. I've attached a screen shot. Any ideas?

 

[pbaldy]

DLookups are fairly simple once you understand them, but they are picky about certain things. You already have the link I would have posted to help you, so I'm surprised that your formula does not incorporate the techniques mentioned in the link. This would be an example for your situation:

DLookup("FieldName", "TableName", "Criteria = '" & forms!FormName!ControlName & "'")

Note how it's different than yours? Hint: the quotes, "&", and apostrophe's (for a text value) are all required.

 

[mab9]

Ah that'd be my misudnerstanding. From reading through that other link, I thought they were using the &'s and ""s for some custom concatinations. It seemed odd to put quotations around everything so I hadn't tried that yet. I'll give it a shot.

 

[mab9]

After doing some more digging, I found some of the alterations I need to use based on composite keys.

http://www.access-programmers.co.uk/forums/showthread.php?t=46627&highlight=Dlookup+Composite

Based on that thread, I've come up with...

=DLookup("[R12 Sales $]", "[qry_sales_by_family_w_analysis]", "[Team Name]='" & cboTeam_Code & "' AND " & _
"[PM Code]= '" & cboPM_Code & "' AND " & _
"[Family Name]= '" & cboPM_Family & "'")

However I keep getting a syntax error with this. I think my eyes are a little tired from staring at this for so long as I can't find it to save my life. Ideas?

 

[pbaldy]

Are all 3 of those fields text data types in the table? Can you post a sample db?

 

[mab9]

Correct, all 3 are text fields. I'll upload a portion of the DB tonight/tomorrow.

 

[mab9]

Here is the sample DB. I've stripped out the majority of the info & coding in it but the basic fields I'm trying to drive the dlookup off of are in there.

Once you open the DB, go to the "Select_PMgr" form. Once open, click the "New Program" button and just hit enter. Once the 2nd form comes up, what I'm trying to do is have the user choose

1. Team Code (A)
2. PM Code (A1)
3. Family (Hammers).

They are cascasding combo's so start with team & move down. Normally they requery each other if changes are made but that got killed when I was stripping this down.

Once those 3 fields are chosen, I'm trying to grab the sales dollar figure that is in the query titled: "qry_sales_by_family_w_analysis".

 

[pbaldy]

This is why I like samples; hard to pick up on this from a description. You've got team name in your formula, where you really want team code. This appears to work:

=DLookUp("[R12 Sales $]","[qry_sales_by_family_w_analysis]","[Team code]='" & [cboTeam_Code] & "' AND " & "[PM Code]= '" & [cboPM_Code] & "' AND " & "[Family Name]= '" & [cboPM_Family] & "'")

I made this the control source of the textbox. You had VBA line continuation characters in your last sample, so I don't know if you wanted it in code, but that's easy to change.

 

[mab9]

Ah there it is. Should've picked up on that, guess I was working with too long to notice. Thanks for the help, really appreciate it.