Help!.....with form code

[Bob Vanderslice]

I have a form with a ComboBox that I want to automatically insert a value
into another field. Basically, I'm pulling from a table that has two
fields....when I choose "FieldA" I want "FieldB" to automatically populate.
I've done this before, but always when there were not multiple occurrences
of FieldA. Trouble is, this time there are many of FieldA......FieldB is
unique within FieldA. Such as:

Project1......Task1
Project1......Task2
Project1......Task3
Project1......Task4
Project1......Task5
Project2......Task1
Project2......Task2
Project2......Task3
Project3......Task1
Project3......Task2

Currently, I'm using the following.....but when I select a given Project, it
will then only select the first TaskNo for that Project. How do I get it to
select the specific one I want to choose.....for example, Project1...Task4
instead of Project1....Task1?

Thanks,
Bob


Private Sub Combo32_BeforeUpdate(Cancel As Integer)

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblProject-TaskNo", dbOpenDynaset)

rst.FindFirst "Proj = " & Chr(34) & Me.Combo32.Value & Chr(34)

If Not rst.NoMatch Then
Me.task_number.Value = rst!TaskNo

End If

rst.Close
Set db = Nothing

End Sub

 

[Dan Artuso]

Hi,
Give this a try. You can refer to a column's value in a combo
with the Column property.

rst.FindFirst "Proj = " & Chr(34) & Me.Combo32 & Chr(34) & _
" And Task = " & Chr(34) & Me.Combo32.Column(1) & _
Chr(34)

just substitute the correct field name.

 

[John Vinson]

How do I get it to
select the specific one I want to choose.....for example, Project1...Task4
instead of Project1....Task1?

How is Access going to know which one you want to insert? Is there any
other information that would make it possible to decide between Task1
and Task4?

 

[Bob Vanderslice]

-----Original Message-----


How is Access going to know which one you want to insert? Is there any
other information that would make it possible to decide between Task1
and Task4?


.

John,

When I inserted the Combo box, I included two columns--
first the Project; then the Task. Since they all show up
individually, I figured if you clicked on Proj1; Task4, it
would insert it; but it always goes and grabs the first
one.

I got the code I'm using from someone on the NG a while
back, and it works great normally, but I've never
encountered a duplication like I currently am in the
Project column. I guess it doesn't work for that scenario
(you've probably figured out by now that I'm not an expert
in code. I've picked up a little along the way (generally
whatever is necessary to accomplish what I'm currently
trying to do), but usually come to the NG when I need to
get something running in a hurry). Anyway, to answer your
question, that's how I figured Access knew when I wanted
Task1 and when I wanted Task4.

Thanks,
Bob

 

[John Vinson]

I got the code I'm using from someone on the NG a while
back, and it works great normally, but I've never
encountered a duplication like I currently am in the
Project column. I guess it doesn't work for that scenario
(you've probably figured out by now that I'm not an expert
in code. I've picked up a little along the way (generally
whatever is necessary to accomplish what I'm currently
trying to do), but usually come to the NG when I need to
get something running in a hurry). Anyway, to answer your
question, that's how I figured Access knew when I wanted
Task1 and when I wanted Task4.

Ok, I'm still confused. The RowSource of the combo contains both
fields; which is the Bound Column? What's the Control Source? You
shouldn't need any code at all to store Task4 if the second column of
the combo is the Bound Column and the combo is bound to the field
which should contain the task. Maybe I'm not understanding the
context!