Use combo box as record selector

  • Thread starter Thread starter Jonathan Blitz
  • Start date Start date
J

Jonathan Blitz

I have a parent/subform.

The parent has an Id field and a description and the subform uses the ID
field from the parent as the linking field.

I wish to add a combo box on the parent to enable the user to select the
required record and display it's subform.
The user must not update the data of the parent.

How do I do this?

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
I have a parent/subform.
The parent has an Id field and a description and the subform uses the ID
field from the parent as the linking field.

I wish to add a combo box on the parent to enable the user to select the
required record and display it's subform.
The user must not update the data of the parent.
Click to expand...

See the following page at The Access Web:

Forms: Have the form move to the record selected in List/Combo box
http://www.mvps.org/access/forms/frm0005.htm
 
I tried that before I posted.

I got the message: Object does not support this method or property.

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
I tried that before I posted.
I got the message: Object does not support this method or property.
Click to expand...

Just a couple of questions to provide some insight into what your scenario is:

*Which version of Access are you using?
*Is this an MDB file?
*Is the bound column in the combo box the one containing the ID value?
*Does the form's "RecordSource" contain the ID field?
*Can you determine which line is generating the error?

:-)
 
Place an unbound combo on your parent form and populate its RowSource with
the RecordID numbers. In its AfterUpdate event place SQL for the records
containing a Where clause detailing the value of the combo, e.g. Where
"RecordID = " & Me.Combobox...if its a number or Where "RecordID = '" &
Me.Combobox & "'" if its a string. The subform is linked to the Parent and
will reflect this...

Set your form field properties Enabled and Locked appropriately to prevent
changes...

HTH.

Tom.
 
I suspect you will have to unbind your form and subform, bind the subform
(if its a datasheet) to a temporary table, and load the temporary table
("docmd.runsql") according to the value of your combo box selection.

John S.
Aylmer, PQ
 
1. Using Access Xp
2. ADP - connect to SQL Server
3. The combo box IS bound to an ID fie3ld
4. Yes
5. The error is in the line: Me.RecordsetClone.Findfirst "[ID] = " &
Me.Month

--
Jonathan Blitz
AnyKey Limited
Israel

"When things seem bad
Don't worry and shout
Just count up the times
Things have worked themselves out."
 
1. Using Access Xp
2. ADP - connect to SQL Server
3. The combo box IS bound to an ID fie3ld
Click to expand...

The combo box must *not* be bound to a field (the "Control Source" property
setting must be left empty). You *do* need to set the combo box's "Bound Column"
property to the same column that contains the ID field value.
4. Yes
5. The error is in the line: Me.RecordsetClone.Findfirst "[ID] = " &
Me.Month
Click to expand...

Being an ADP, the code must be modified, as the "FindFirst" method isn't
available. Instead, try replacing the code in the combo box's "AfterUpdate"
event procedure with the following:

'***EXAMPLE START
Dim rs As Object
'Set variable to form's recordsetclone property
Set rs = Me.RecordsetClone
'Find the record
rs.Find "ID=" & Me.Month.Value
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
'***EXAMPLE END
 
Back
Top