Update second field based on ComboBox 1

[hysonmb]

My problem is that I'm trying to use a combobox based on one table to automatically insert a value stored in a another table on the form that I'm working on. Right now I have these tables:

tblGrade
tblSqft

Which are linked with a one to many relationship. On the form, I want to be able to select a grade and have the corresponding Sqft fill in automatically. I have cboGradeID and cboSqftID on the form and I can individually select each, but the end user wants the second to automatically update once the first is selected.

I hope that this question makes sense. I've looked around that forum and tried what I've seen, but, none of it has worked. Please, if you do respond, make the response as basic (Barney style) as possible.
Thanks in advance.

 

[luceze]

You could add this code to your cboGradeID's after update event:

Private Sub cboGradeID_AfterUpdate()
sqlStr = "SELECT tblSqft.Sqft FROM tblSqft WHERE (((tblSqFt.Grade)=" & cboGradeID & &quot);"
Me.cboSqFtID.RowSource = sqlStr
me.cboSqFtID.requery
End Sub

Let me know if this works.

HTH,
Eric

 

[hysonmb]

Well, I put this in there on the AfterUpdate event and it didn't come back with an error, but it didn't update the other field either. Should they be unbound?

 

[luceze]

On reading through this post again there might be a different solution. If you only want to fill in the value and there is only one square feet value for each gradeID then you can use a dlookup to insert the value. In this case I would use a text box instead of a combo box since the user will not need to select from a list of values. In any case, you can use this code in the after update event of your Grade combo box:

Private Sub cboGradeID_AfterUpdate()
Dim varSqft
VarSqft = DLookup("[SqFt]", "tblSqft", "[GradeID]=" & "'" & Me!cboGradeID & "'&quot
me.cbuSqFt.value=VarSqft
me.cboSqFt.requery
End Sub

Note: If your tables are very large, Dlookups can be very slow. However on small tables they tend to work pretty well.

HTH,
Eric

 

[pdldavis]

Hi, go to design view. Go to cboGradeID properties. Double click in the After Update row to invoke the code builder.

Place: Me.cboSqftID.Requery

between

Private Sub cboGradeID_AfterUpdate()

End Sub

so it looks like:

Private Sub cboGradeID_AfterUpdate()
Me.cboSqftID.Requery
End Sub

That should do it for you if the two tables are linked correctly.

 

[luceze]

The first set of code only updates the row source (the list to choose from) of your second combo box. The second piece of code will actually fill in the value in the combo.

You will also need to make sure that the name of the fields in your square feet table are GradeID and SqFt respectively.

Also in the second piece of code I made a typo.
Change:
me.cbuSqFt.value=VarSqft
To
me.cboSqFt.value=VarSqft

HTH,
Eric

 

[hysonmb]

I think that my tables must not be linked properly. When I put the code in as suggested, it returns a compile error and takes me back to the VBA screen with the Handler for the AfterUpdate event highlighted. I'm going to try messing around with the relationships and see if there is a mistake in there.

 

[luceze]

What line are you getting an error on?

 

[hysonmb]

OK, I got it to run the cycle, but now it only uses the very first entry in the eblSqft. I assume that certifies a bad relationship?
Thanks for all of the help guys! You're teaching me a lot.

 

[hysonmb]

The line was the handler, but now it gets through there as I was saying in the last post that I just sent. And I made a typo, my table it tblSqft rather than eblSqft.

 

[hysonmb]

I've done everything that I can think to do and nothing has worked yet. When the SqftID does the requery, it shows the first entry in the Sqft table, no matter which entry I select in the Grade field. I tried changing the relationship, but it didn't do anything.
The way I have it set up right now:

tblSqft:
SqftID
Sqft
Desc (for reference)
GradeID

tblGrade:
GradeID
Grade
Desc
Sqft (for set up reference)

I have the SqftID related as a one-to-many. Is this wrong? If so, what should I do?

 

[luceze]

If you want you can send me a copy of your DB (Get rid of any confidential data first) and I'll take a look at it.
eisom@esc11.net

 

[hysonmb]

I've sent it. Thanks again.

 

[luceze]

I just sent it back. I changed the dlookup code to look for numbers instead of text. I changed the code to return the SqFtID since that was the control source of your text box. I also added a text box to your form that will show the square feet instead of just the SqFtID.

Post back if you have any questions.

Eric

 

[hysonmb]

Thank you again and again. This is outstanding!