B
BobV
I'm working in a form with a combo field that grabs its data from a query,
and am trying to get the selection to auto-fill another corresponding field
on the form from that same query. For example, here are some of the lines
from the query:
Admin MailCenter
Admin Supplies
Admin Misc
Comms Phones
Comms RemoteAccess
Comms Pagers
Comms Misc
Pers Salaries
Pers Overtime
Pers Upgrades
and so on......
Main table is called tblSpendplan; table the combobox is accessing for
selection data is tblProject-TaskNo. Applicable fields in tblSpendplan are
proj_id and task_number; fields in tblProject-TaskNo are Proj and TaskNo.
In the combo box, name is Combo101 and Control source is Proj_id. Row
source is:
SELECT DISTINCTROW [qryProject-TaskNo].[Proj], [qryProject-TaskNo].[TaskNo]
FROM [qryProject-TaskNo]. Column count is 2; bound column is 1. Think
that's all that applies.
In the form code, here's what I have:
*****************************************************
Private Sub Combo101_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.Combo101.Value & Chr(34)
If Not rst.NoMatch Then
Me.task_number.Value = rst!TaskNo
End If
rst.Close
Set db = Nothing
End Sub
*******************************************************
For the findfirst line, have also tried:
rst.FindFirst "Proj = " & "'" & Me.Combo101.Value & "'" & ""
.....it didn't help.
What happens on the form is that when I click the drop-down and select a
Project-TaskNo combination (which displays properly) the Project fills in
okay, but the TaskNo displayed is whatever the first task number of that
particular project happens to be. And once I select a particular project,
it won't allow me to reselect any other task in that project--only the first
one. If I click on another project, it displays the first one in that
project, and so on......always, and only, the first task number under a
given project.
What am I missing......I haven't done this in a while, and I'm not a code
expert, but I do have several other fields like this in this and other dbs
and have never encountered this. I've compared it to the others, attempted
some research, and just don't see what I'm doing wrong.
Remember, I'm not an expert, so please be kind......
Thanks in advance for any help you can provide,
Bob
and am trying to get the selection to auto-fill another corresponding field
on the form from that same query. For example, here are some of the lines
from the query:
Admin MailCenter
Admin Supplies
Admin Misc
Comms Phones
Comms RemoteAccess
Comms Pagers
Comms Misc
Pers Salaries
Pers Overtime
Pers Upgrades
and so on......
Main table is called tblSpendplan; table the combobox is accessing for
selection data is tblProject-TaskNo. Applicable fields in tblSpendplan are
proj_id and task_number; fields in tblProject-TaskNo are Proj and TaskNo.
In the combo box, name is Combo101 and Control source is Proj_id. Row
source is:
SELECT DISTINCTROW [qryProject-TaskNo].[Proj], [qryProject-TaskNo].[TaskNo]
FROM [qryProject-TaskNo]. Column count is 2; bound column is 1. Think
that's all that applies.
In the form code, here's what I have:
*****************************************************
Private Sub Combo101_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.Combo101.Value & Chr(34)
If Not rst.NoMatch Then
Me.task_number.Value = rst!TaskNo
End If
rst.Close
Set db = Nothing
End Sub
*******************************************************
For the findfirst line, have also tried:
rst.FindFirst "Proj = " & "'" & Me.Combo101.Value & "'" & ""
.....it didn't help.
What happens on the form is that when I click the drop-down and select a
Project-TaskNo combination (which displays properly) the Project fills in
okay, but the TaskNo displayed is whatever the first task number of that
particular project happens to be. And once I select a particular project,
it won't allow me to reselect any other task in that project--only the first
one. If I click on another project, it displays the first one in that
project, and so on......always, and only, the first task number under a
given project.
What am I missing......I haven't done this in a while, and I'm not a code
expert, but I do have several other fields like this in this and other dbs
and have never encountered this. I've compared it to the others, attempted
some research, and just don't see what I'm doing wrong.
Remember, I'm not an expert, so please be kind......
Thanks in advance for any help you can provide,
Bob