Default value in a form return a value from a table 1

[gxd135]

I want the default value of a field on a form to access a table and return the value in the table when another field is equal.

I have a table like this
col a col b
Diffusers 1
grilles 1
VAVs 3

The form has a drop down to select column a, and the next field you enter the value for column b which changes sometimes, but is typically 1 if diffusers, 3 if vav's. I tried to use the dlookup function, but I am not getting anywhere. Any help is appreciated.

 

[rdodge]

Assuming that "ControlA" is either a combobox or listbox with 2 columns in it, and "ControlB" is a textbox

If "ControlA" is a combobox

Private Sub ControlB_AfterUpdate()
Dim I As Long
For I = 0 To ControlA.ListCount - 1 Step 1
If ControlA.Column(1,I) = CStr(ControlB.Value) Then
ControlA.Value = ControlA.Column(0,I)
Exit For
End If
Next I
End Sub

If "ControlA" is a listbox

Private Sub ControlB_AfterUpdate()
Dim I As Long, J As Long
For I = 0 To ControlA.ListCount - 1 Step 1
If ControlA.Column(1,I) = CStr(ControlB.Value) Then
For J = 0 To ControlA.ListCount - 1 Step 1
ControlA.Selected(J) = 0
Next J
ControlA.Selected(I) = -1
Exit For
End If
Next I
End Sub


Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[gxd135]

Control A is a combobox, and b a textbox. But there are multiple rows in conrtol a. Also where do I put this code. I am not a programmer, and do not use vb much. Do I put it in the default value of the textbox?

 

[rdodge]

On your form, do the following steps

Right Click on Control B

Click on Properties

Click on Event Tab

Click on the text box that is to the right of "After Update"

Click on the button to the right of it, which has the 3 dots

Click on "Code" (provided you haven't set it up yet)

Now, paste the code below at the point the cursor has taken you to.

Dim I As Long
For I = 0 To ControlA.ListCount - 1 Step 1
If ControlA.Column(1,I) = CStr(ControlB.Value) Then
ControlA.Value = ControlA.Column(0,I)
Exit For
End If
Next I

You can now click on the "Save" button, and confirm to save, if asked to save on more than 1 object (Form, Standard Module, Class Module)

Please note, the code above is assuming that your combo box has multiple columns. If it does not, paste the below code in instead of the above code:

Dim I As Long, RS as DAO.Recordset
Set RS = Me.RecordsetClone
RS.FindFirst &quot;<TableName>.<FieldName> = &quot; & CStr(Me.ControlB.Value)
If NoMatch = false Then
ControlA.Value = RS.Fields(0).Value
End If

Note:

<TableName> is where you put in the name of the table without the angle brackets

<FieldName> is where you put in the name of the field without the angle brackets

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[rdodge]

Opps, just realized, where I put in:

NoMatch

It must be:

RS.NoMatch

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[gxd135]

So I am Pretty Close.

Control A should be the name of my combo box, and control b should be the name of my text box which I want to update correct?

Here is my code:
For I = 0 To EquipmentType.ListCount - 1 Step 1
If EquipmentType.Column(1, I) = CStr(InstallTime.Value) Then
EquipmentType.Value = EquipmentType.Column(0, I)
Exit For
End If
Next I

Where EquipmentType is the name of the combo box, and Install Time is the name of my text box which I want to update. What am I doing wrong?

 

[rdodge]

You have this code in the AfterUpdate Event of your InstallTime Textbox, right?

Again, just as a double check, the second column of your combo box is the same data as the textbox, right?

The Bound column of your combo box is set to 1, which is the first column of the combo box, right?

I would say use the ListIndex to set the active item, but the only time when you can manipulate the ListIndex is when the combobox/listbox has the focus.

Provided all of the above is true, the value in the combo box should change when you update the data in your textbox.

The only other thing for you to do is to put the &quot;Me.&quot; in front of the control names, which was my mistake from earlier, but I have seen it work at times without that.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[gxd135]

I checked those things, and its not working. I want the text box to be the one that changes, not the combo box as ou say up there. What do you mean by the second column of the combo box being the same as the text box. The second Column of the table that this all runs off of is what I want to be returned in the text box.

EX:

Combo Box Text Box
Equipment Type Install Time

Diffusers 1
Grilles 2
VAV's 3

The combo box is a drop down in the Equipment Subform called <EquipmentType>, and the Text Box is a text box in the same form called <InstallTime>. In the after update I put the following code, and that is as far as I have gotten...

For I = 0 To EquipmentType.ListCount - 1 Step 1
If EquipmentType.Column(1, I) = CStr(InstallTime.Value) Then
EquipmentType.Value = EquipmentType.Column(0, I)
Exit For
End If
Thanks so much for your help

 

[rdodge]

Aw, so you are changing the Combobox. If that's the case, then use the following code in the Combobox's AfterUpdate Event to change the textbox.

If Me.EquipmentType.ListIndex > -1 Then
Me.InstallTime.Value = Me.EquipmentType.Column(1)
End If

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[gxd135]

Works Great Thanks a lot

 

[rdodge]

Not a problem. Sorry about the confusion earlier.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[gxd135]

Another question if you don't mind. I have another field in this form that is shop time. It is the column after the Install Time in my table and I want it to act the same way can't I just put the following:

Me.InstallTime.Value = Me.EquipmentType.Column(1)
Me.ShopTime.Value = Me.EquipmentType.Column(2)

I tried that, and it does not work any ideas?

 

[rdodge]

Sorry for the long delay as I was on vacation over the holidays and last week was my year end reporting week.

Is the Shop time including in the listbox as a column?

I'm not really sure what you have set in the different properties of the listbox like the RowSource, RowSourceType, and ColumnCount.

Assuming that it is in the third column as you stated, you may need to compare the properties of both properties, the data type of the third column (String if RowSourceType is ListValues) and the ShopTime Control.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[gxd135]

Yes it is the third column. I am not really sure how to explain it, but here goes. This is what I have on my form:

Equipment Type Shop Time Install Time

There are about 50 different equipment types, and a shop time and install time to go along with each. When you select the Equipment type, right now it pulls the Install Time correct. This is the code I am using:

Private Sub Install_Time_AfterUpdate()
Dim I As Long
For I = 0 To EquipmentType.ListCount - 1 Step 1
If EquipmentType.Column(1, I) = CStr(InstallTime.Value) Then
EquipmentType.Value = EquipmentType.Column(0, I)
Exit For
End If
Next I

End Sub

Private Sub EquipmentType_AfterUpdate()
If Me.EquipmentType.ListIndex > -1 Then
Me.InstallTime.Value = Me.EquipmentType.Column(1)
Me.ShopTime.Value = Me.EquipmentType.Column(1)
End If
End Sub

Private Sub Shop_Time_AfterUpdate()


End Sub


The table on the back end is the following

Equipment Type Install Time Shop Time
Diffuser 1 0
Grille 1 1
VD 2 2

Where the first row is the column names.

 

[rdodge]

Assuming you intended the line:

Me.ShopTime.Value = Me.EquipmentType.Column(1)

to be:

Me.ShopTime.Value = Me.EquipmentType.Column(2)

I don't see why it shouldn't work. Are you getting any kind of an error message when the AfterUpdate event runs?

Now, keep in mind, the data returned by the Column property will be in string data type, if the RowSourceType is ListValues.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[gxd135]

Yeah thats what I figured before and I tried that, and it didn't work. I tried it again, and no luck. Here is my code now:

Private Sub Install_Time_AfterUpdate()
Dim I As Long
For I = 0 To EquipmentType.ListCount - 1 Step 1
If EquipmentType.Column(1, I) = CStr(InstallTime.Value) Then
EquipmentType.Value = EquipmentType.Column(0, I)
Exit For
End If
Next I

End Sub

Private Sub EquipmentType_AfterUpdate()
If Me.EquipmentType.ListIndex > -1 Then
Me.InstallTime.Value = Me.EquipmentType.Column(1)
Me.ShopTime.Value = Me.EquipmentType.Column(2)
End If
End Sub

Private Sub Shop_Time_AfterUpdate()


Not too sure what I am doing wrong??

 

[rdodge]

Other than maybe to use a CStr around the portion of the code that has the Column Property given it's of a Variant type data when the combobox is bound, I'm basically out of ideas.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[gxd135]

Can I write two totally seperate sets of code. It seems to work for the one, but not the other. Confusing

 

[rdodge]

In what way do you mean?

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.