Adding Individual CBox Values to an Unbound Text Box

[csperati]

Good morning. I've been scouring forums for the past few days trying to figure this out to no avail. I am new to Access, watching learning videos and such to teach myself in order to have a good base for a new job that I will be starting soon, so I apologize if this comes across as a dumb question.

To practice, I have put together a small database from a game that outputs data to a form using expressions (this is predominantly how the customers use Access). It is going well, but I'm stuck on one aspect. I have three unbound combo boxes that get data from three different tables, the values of which are added together in one unbound text box. The expression I have come up with for the control source of the text box is as follows:

=DLookUp("Base","ThreatRating","TRID=" & Nz([TRCBox],0))+DLookUp("Armor","ThreatRating","TRID=" & Nz([TRCBox],0))+DLookUp("Impact","FoeType","TypeID=" & Nz([TypeCBox],0))+DLookUp("Impact","ArmorsType","ArmorsID=" & Nz([ArmorCBox],0))

It works great, except that nothing shows up in the text box until all three combo boxes are selected. Is there any way, in this type of expression, to have each selection show up individually so that if nothing is selected from one combo box, the unbound text box still shows a value from the others?

Thanks for any assistance you can offer.

 

[Ranman256]

there's no need to use DLOOKUPs. (they are slow).
instead , the combo box will pull 2 fields: the value the user sees, and the value in the table you want to use.
Instead of dlookup, just set the boundcol of the combo = 2.

The final textbox is assembled everytime a user changes 1 of the 3.

sub TRCBox_afterupdate()
BuildTxtBox
end sub

sub TypeCBox_afterupdate()
BuildTxtBox
end sub

sub ArmorCBox_afterupdate()
BuildTxtBox
end sub


'----------------
sub BuildTxtBox()
'----------------
dim sWhere as string 

sWhere = "1=1"
if not IsNUll(TRCBox) then sWhere = sWhere & " and [TRID]='" & TRCBox & "'"
if not IsNUll(TypeCBox) then sWhere = sWhere & " and [TypeID]='" & TypeCBox & "'"
if not IsNUll(ArmorCBox) then sWhere = sWhere & " and [ArmorsID]='" & ArmorCBox & "'"

txtBox = sWhere
end sub

 

[csperati]

Thank you for the quick response. I apologize, but I don't understand your solution.

From my limited understanding, I know dlookups are not the best way to do things and there seems to be many different methods of getting the data, however, from my understanding dlookupos are one of the primary functions that the customers I will be supporting use so I'm trying to practice specifically with them.

The form is already built; in the example I provided, the unbound textbox in which I have put the expression is named txtimpactrating, so I don't want to build a new one.

I have tried changing the bound column in the combobox to 2, but that only generates a blinking #Error in the field when I make a selection. I'm starting to get the idea that when using dlookups in an expression that it is not possible to have each selection show up individually so that if nothing is selected from one combo box, the unbound text box still shows a value from the others.

 

[Mark_]

DLookup returns NULL if there is no matching value.

For myself, as you have multiple combo boxes already on the screen I would use the values they have already selected. What are the sources for the comboboxes?

 

[csperati]

Each of the combo boxes is a column from a table. There are three different tables that make up this database:

Type
ThreatRating
ArmorsType

and three combo boxes from which I am pulling data to display in a series of formatted unbound text box fields on a form.

TypeCBox
TRCbox
ArmorCBox

Since the combo boxes and text boxes are unbound, I was using dlookup to get the data in the expression and adding the expressions together to get the value. I am new to Access and since expressions are what I am going to be supporting, that is where I have focusing.

 

[Pat Hartman]

Part of Ranman's suggestion was correct. You do want to include the additional columns in the combo itself. You DO NOT however want to change the bound column. Leave that alone.

The columns of a combo are a zero based array. The first column (which is also normally the bound column) is .Column(0) and so people simply refer to it as me.myCombo since the bound column is the default. When you refer to the other columns, you have to use their correct column index value. So, the SECOND column is .Column(1) (remember 0 is the first column) and the THIRD column is .Column(2).

That makes your expression:

= Nz([TRCBox].Column(2),0)) + Nz([TRCBox].Column(3),0)) + DLookUp("Impact","FoeType","TypeID=" & Nz([TypeCBox],0)) + DLookUp("Impact","ArmorsType","ArmorsID=" & Nz([ArmorCBox],0)

Of course, if it is possible to change the RowSource query of the combo to include the columns from the other tables, you can do that also. For this example, I assumed that the first two fields were the third and fourth columns of the RowSource of the TRCBox combo.

 

[csperati]

Thanks Pat, as I am still learning, this was a very helpful explanation. It took me a little while and a couple of errors before I realized that the expression you provided goes into the AfterUpdate field in the combobox, also had to remove a couple of parenthesis, which I feel is great practice for troubleshooting. The modified expression is:

=Nz([TRCBox].[Column](2),0)+Nz([TRCBox].[Column](6),0)+DLookUp("Impact","FoeType","TypeID=" & Nz([TypeCBox],0))+DLookUp("Impact","ArmorsType","ArmorsID=" & Nz([ArmorCBox],0))

It seems, however, that this is more complex than I initially understood. You see, the tables each have a number of columns:

FoeType - 12 columns
ThreatRating - 12 columns
ArmorsType - 10 columns

Each of the three comboboxes provide values for several different unbound text boxes; in my initial example I provided only one text box that is affected, Impact Rating, believing that a solution for this one issue could be copied, pasted, and modified for each.

Is there a way to generalize the columns to the entire table, rather than listing specific columns?

 

[Mark_]

@csperati

You may want to read through THIS THREAD to get some ideas about filling in multiple fields based on the selection from ComboBox.

One for each combobox you'll be using, then have your calculation based on the values looked up. In reality not too difficult.

 

[csperati]

I had bookmarked this page from a previous search through the forums; at the moment it makes little sense to me, but I'm sure as I learn more it will be helpful. Thank you.

 

[Pat Hartman]

When an expression starts with the "=", it is assumed to be the ControlSource for a control. That is what you posted so I used the same method. It does not go into an event procedure.

If the other columns come from other combos, use the same technique to obtain them. Add the extra columns to the relevant combo and refer to the column by using the .column(?) property. Just specify the correct column number.

I've attached a sample database that may help you to understand how to work with the columns of a combo or listbox.