Option Group and Lookup Record combo box

L

Linda RQ

Hello Everyone,

I made the mistake of trying to get a little fancy and agreed to try some
code with help....but the help is out of town now. I need help
troubleshooting my combo box. I have 2 on my form. One to look up patients
by last name and one to look up my patients by room number. I am assuming
if I can get the one fixed, I can do the other. I have copy and pasted the
code below. The first sub is for the combo box and the second is for my
option group. I finally got my option group to work after a huge struggle
and I suspect the combo box doesn't work because of my option group or maybe
it's the underlying query for the combo? I pasted the sql for my cboqry
here too in case that will help. If I can get this to work, I think I'll be
happy with my database and I promise not to use code ever again!...Thanks,
Linda

Private Sub Combo73_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PtLName] = '" & Me![Combo73] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Row Source is this query

SELECT qryActivePatients.PtID, qryActivePatients.PtLocRmNum,
qryActivePatients.PtLName, qryActivePatients.PtFName
FROM qryActivePatients
ORDER BY qryActivePatients.PtLocRmNum, qryActivePatients.PtLName,
qryActivePatients.PtFName;

Column count is 4
Column widths are 0";0.25";0.5";0.5"



Private Sub opgpActiveAndInactivePatients_AfterUpdate()

If Me.opgpActiveAndInactivePatients = 1 Then
Me.RecordSource = "qryActivePatients"
Me.Requery
Me.sfmPtLocation.Form.RecordSource = "qrysbfrmPtLocation"
Me.sfmPtLocation.Requery
Me.sfmPtThpy.Form.RecordSource = "qryfrmPtThpy"
Me.sfmPtThpy.Requery
Me.Combo78.RowSource = "qrycboLocatePtByLName"
Me.Combo78.Requery
Me.Combo73.RowSource = "qrycboLocatePtByRm"
Me.Combo73.ColumnCount = 4
Me.Combo73.Requery

Else
Me.RecordSource = "qryInactivePatients"
Me.Requery
Me.sfmPtLocation.Form.RecordSource = "qrysbfrmPtLocationWithEndDate"
Me.sfmPtLocation.Requery
Me.sfmPtThpy.Form.RecordSource = "qryfrmPtThpyWithEndDate"
Me.sfmPtThpy.Requery
Me.Combo78.RowSource = "qrycboLocatePtByLNameInactive"
Me.Combo78.Requery
Me.Combo73.RowSource = "qrycboLocatePtByRmInactive"
Me.Combo73.ColumnCount = 5
Me.Combo73.Requery


End If



End Sub
 
C

Carl Rapson

We're missing one important piece of information: what is the bound column
for the combo box? If it's 1, then your problem is that the combo box is
returning the PtID value (thr first column in the query), but you're wanting
the PtLName value (the third column in the query). The easiest way to fix
this is to refer to the third column as follows:

rs.FindFirst "[PtLName] = '" & Me.Combo73.Column(2) & "'"

Note that the Column is zero-based, so the third column is number 2. Or, you
could go ahead and use the PtID as follows:

rs.FindFirst "[PtID] = " & Me.Combo73

I'm assuming that the PtID is numeric, so no single quotes are required.
Even though the PtID doesn't show up in the combo box window, if it's the
bound column then you can still use it.

Carl Rapson

Linda RQ said:
Hello Everyone,

I made the mistake of trying to get a little fancy and agreed to try some
code with help....but the help is out of town now. I need help
troubleshooting my combo box. I have 2 on my form. One to look up
patients by last name and one to look up my patients by room number. I am
assuming if I can get the one fixed, I can do the other. I have copy and
pasted the code below. The first sub is for the combo box and the second
is for my option group. I finally got my option group to work after a
huge struggle and I suspect the combo box doesn't work because of my
option group or maybe it's the underlying query for the combo? I pasted
the sql for my cboqry here too in case that will help. If I can get this
to work, I think I'll be happy with my database and I promise not to use
code ever again!...Thanks, Linda

Private Sub Combo73_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PtLName] = '" & Me![Combo73] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Row Source is this query

SELECT qryActivePatients.PtID, qryActivePatients.PtLocRmNum,
qryActivePatients.PtLName, qryActivePatients.PtFName
FROM qryActivePatients
ORDER BY qryActivePatients.PtLocRmNum, qryActivePatients.PtLName,
qryActivePatients.PtFName;

Column count is 4
Column widths are 0";0.25";0.5";0.5"



Private Sub opgpActiveAndInactivePatients_AfterUpdate()

If Me.opgpActiveAndInactivePatients = 1 Then
Me.RecordSource = "qryActivePatients"
Me.Requery
Me.sfmPtLocation.Form.RecordSource = "qrysbfrmPtLocation"
Me.sfmPtLocation.Requery
Me.sfmPtThpy.Form.RecordSource = "qryfrmPtThpy"
Me.sfmPtThpy.Requery
Me.Combo78.RowSource = "qrycboLocatePtByLName"
Me.Combo78.Requery
Me.Combo73.RowSource = "qrycboLocatePtByRm"
Me.Combo73.ColumnCount = 4
Me.Combo73.Requery

Else
Me.RecordSource = "qryInactivePatients"
Me.Requery
Me.sfmPtLocation.Form.RecordSource = "qrysbfrmPtLocationWithEndDate"
Me.sfmPtLocation.Requery
Me.sfmPtThpy.Form.RecordSource = "qryfrmPtThpyWithEndDate"
Me.sfmPtThpy.Requery
Me.Combo78.RowSource = "qrycboLocatePtByLNameInactive"
Me.Combo78.Requery
Me.Combo73.RowSource = "qrycboLocatePtByRmInactive"
Me.Combo73.ColumnCount = 5
Me.Combo73.Requery


End If



End Sub
Click to expand...
 
L

Linda RQ

Hey, the bound column is 1 which is an auto id number. Ok...let me get to
work.

Thanks!

Carl Rapson said:
We're missing one important piece of information: what is the bound column
for the combo box? If it's 1, then your problem is that the combo box is
returning the PtID value (thr first column in the query), but you're
wanting the PtLName value (the third column in the query). The easiest way
to fix this is to refer to the third column as follows:

rs.FindFirst "[PtLName] = '" & Me.Combo73.Column(2) & "'"

Note that the Column is zero-based, so the third column is number 2. Or,
you could go ahead and use the PtID as follows:

rs.FindFirst "[PtID] = " & Me.Combo73

I'm assuming that the PtID is numeric, so no single quotes are required.
Even though the PtID doesn't show up in the combo box window, if it's the
bound column then you can still use it.

Carl Rapson

Linda RQ said:
Hello Everyone,

I made the mistake of trying to get a little fancy and agreed to try some
code with help....but the help is out of town now. I need help
troubleshooting my combo box. I have 2 on my form. One to look up
patients by last name and one to look up my patients by room number. I
am assuming if I can get the one fixed, I can do the other. I have copy
and pasted the code below. The first sub is for the combo box and the
second is for my option group. I finally got my option group to work
after a huge struggle and I suspect the combo box doesn't work because of
my option group or maybe it's the underlying query for the combo? I
pasted the sql for my cboqry here too in case that will help. If I can
get this to work, I think I'll be happy with my database and I promise
not to use code ever again!...Thanks, Linda

Private Sub Combo73_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PtLName] = '" & Me![Combo73] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Row Source is this query

SELECT qryActivePatients.PtID, qryActivePatients.PtLocRmNum,
qryActivePatients.PtLName, qryActivePatients.PtFName
FROM qryActivePatients
ORDER BY qryActivePatients.PtLocRmNum, qryActivePatients.PtLName,
qryActivePatients.PtFName;

Column count is 4
Column widths are 0";0.25";0.5";0.5"



Private Sub opgpActiveAndInactivePatients_AfterUpdate()

If Me.opgpActiveAndInactivePatients = 1 Then
Me.RecordSource = "qryActivePatients"
Me.Requery
Me.sfmPtLocation.Form.RecordSource = "qrysbfrmPtLocation"
Me.sfmPtLocation.Requery
Me.sfmPtThpy.Form.RecordSource = "qryfrmPtThpy"
Me.sfmPtThpy.Requery
Me.Combo78.RowSource = "qrycboLocatePtByLName"
Me.Combo78.Requery
Me.Combo73.RowSource = "qrycboLocatePtByRm"
Me.Combo73.ColumnCount = 4
Me.Combo73.Requery

Else
Me.RecordSource = "qryInactivePatients"
Me.Requery
Me.sfmPtLocation.Form.RecordSource = "qrysbfrmPtLocationWithEndDate"
Me.sfmPtLocation.Requery
Me.sfmPtThpy.Form.RecordSource = "qryfrmPtThpyWithEndDate"
Me.sfmPtThpy.Requery
Me.Combo78.RowSource = "qrycboLocatePtByLNameInactive"
Me.Combo78.Requery
Me.Combo73.RowSource = "qrycboLocatePtByRmInactive"
Me.Combo73.ColumnCount = 5
Me.Combo73.Requery


End If



End Sub
Click to expand...
Click to expand...
 
L

Linda RQ

Well...some success. I tried both ideas below but couldn't get it to work.
I removed my ID field from my query so the bound column is PtLName. The
lookup and go to that record now works when I have the active patients radio
button selected. I'm going to keep working on it and probably start a new
thread since this one is so old.

Linda



Linda RQ said:
Hey, the bound column is 1 which is an auto id number. Ok...let me get to
work.

Thanks!

Carl Rapson said:
We're missing one important piece of information: what is the bound
column for the combo box? If it's 1, then your problem is that the combo
box is returning the PtID value (thr first column in the query), but
you're wanting the PtLName value (the third column in the query). The
easiest way to fix this is to refer to the third column as follows:

rs.FindFirst "[PtLName] = '" & Me.Combo73.Column(2) & "'"

Note that the Column is zero-based, so the third column is number 2. Or,
you could go ahead and use the PtID as follows:

rs.FindFirst "[PtID] = " & Me.Combo73

I'm assuming that the PtID is numeric, so no single quotes are required.
Even though the PtID doesn't show up in the combo box window, if it's the
bound column then you can still use it.

Carl Rapson

Linda RQ said:
Hello Everyone,

I made the mistake of trying to get a little fancy and agreed to try
some code with help....but the help is out of town now. I need help
troubleshooting my combo box. I have 2 on my form. One to look up
patients by last name and one to look up my patients by room number. I
am assuming if I can get the one fixed, I can do the other. I have copy
and pasted the code below. The first sub is for the combo box and the
second is for my option group. I finally got my option group to work
after a huge struggle and I suspect the combo box doesn't work because
of my option group or maybe it's the underlying query for the combo? I
pasted the sql for my cboqry here too in case that will help. If I can
get this to work, I think I'll be happy with my database and I promise
not to use code ever again!...Thanks, Linda

Private Sub Combo73_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PtLName] = '" & Me![Combo73] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Row Source is this query

SELECT qryActivePatients.PtID, qryActivePatients.PtLocRmNum,
qryActivePatients.PtLName, qryActivePatients.PtFName
FROM qryActivePatients
ORDER BY qryActivePatients.PtLocRmNum, qryActivePatients.PtLName,
qryActivePatients.PtFName;

Column count is 4
Column widths are 0";0.25";0.5";0.5"



Private Sub opgpActiveAndInactivePatients_AfterUpdate()

If Me.opgpActiveAndInactivePatients = 1 Then
Me.RecordSource = "qryActivePatients"
Me.Requery
Me.sfmPtLocation.Form.RecordSource = "qrysbfrmPtLocation"
Me.sfmPtLocation.Requery
Me.sfmPtThpy.Form.RecordSource = "qryfrmPtThpy"
Me.sfmPtThpy.Requery
Me.Combo78.RowSource = "qrycboLocatePtByLName"
Me.Combo78.Requery
Me.Combo73.RowSource = "qrycboLocatePtByRm"
Me.Combo73.ColumnCount = 4
Me.Combo73.Requery

Else
Me.RecordSource = "qryInactivePatients"
Me.Requery
Me.sfmPtLocation.Form.RecordSource = "qrysbfrmPtLocationWithEndDate"
Me.sfmPtLocation.Requery
Me.sfmPtThpy.Form.RecordSource = "qryfrmPtThpyWithEndDate"
Me.sfmPtThpy.Requery
Me.Combo78.RowSource = "qrycboLocatePtByLNameInactive"
Me.Combo78.Requery
Me.Combo73.RowSource = "qrycboLocatePtByRmInactive"
Me.Combo73.ColumnCount = 5
Me.Combo73.Requery


End If



End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combo Box - Unique Situation 2
Cascading Combo Box BIG issue 11
Option Group changing Combo Box 3
Option group & combo box relationship 1
Option Group and Combo Box 2
Combo Box selection disappearing on new record and change of option selection 2
Combo box show values based on value in Option group? 3
Filter subform combobox on option button in main form 1

Top