From: Marshall Barton on
Does "go wrong" still mean you are being prompted for
something like:

Forms!formname.subformcontrolname.Form.comboname

If so and you have double checked the spelling of
everything, then I suspect that you have used the name of
the form object displayed in the subform control instead of
the name of the subform control on the main form. The
subform control on the main form is often named the same as
the form object it displays, but they can be different.
--
Marsh
MVP [MS Access]


Hannah wrote:
>Thanks Marshall. I did change the subform record source and I changed it
>using the names of my own controls in place of your dummies so really not
>sure what's making it go wrong? Any ideas?
>
>"Marshall Barton" wrote:
>> The fact that you were prompted to enter somethng means that
>> you misspelled whatever the prompt box was asking for.
>>
>> You may be confused about what you were supposed to do. A
>> combo box does not have a record source, they do have a
>> RowSource. Either way, you should not mess with the combo
>> box.
>
>> Hannah wrote:
>> >Thanks for such a speedy reply! I tried doing what you said but I got a
>> >message saying "Enter Parameter Value" and then the bit of code you gave me.
>> >I've checked I entered it correctly and in the right place but beyond that
>> >I'm a bit stumped about what's causing the problem. Could it be clashing with
>> >other code? I previously had the cboHarbour record source as the Harbour
>> >table.
From: Hannah on
You were right Marshall! That was the problem - thought I'd checked it but
clearly not! Thanks again for being so patient, I'm still horribly bad at
explaining when things don't work for me! So saying, I've hit a bit of a new
issue: when I choose something from my cboHarbour, nothing shows up. I think
this might be to do with the code I have on the Row Source property of the
combo box which is:

SELECT DISTINCTROW [Harbours].[HID], [Harbours].[HarbourName] FROM Harbours
WHERE ((([Harbours].[CID]) Like [forms]![frmTrial]![combo28]));

(Harbours is the actual table). If this is the problem, how do I match it up
with the query my form's now based on? Again, apologies for really bad
explanation! Thanks for your help so much - I feel like I'm finally getting
there.

"Marshall Barton" wrote:

> Does "go wrong" still mean you are being prompted for
> something like:
>
> Forms!formname.subformcontrolname.Form.comboname
>
> If so and you have double checked the spelling of
> everything, then I suspect that you have used the name of
> the form object displayed in the subform control instead of
> the name of the subform control on the main form. The
> subform control on the main form is often named the same as
> the form object it displays, but they can be different.
> --
> Marsh
> MVP [MS Access]
>
>
> Hannah wrote:
> >Thanks Marshall. I did change the subform record source and I changed it
> >using the names of my own controls in place of your dummies so really not
> >sure what's making it go wrong? Any ideas?
> >
> >"Marshall Barton" wrote:
> >> The fact that you were prompted to enter somethng means that
> >> you misspelled whatever the prompt box was asking for.
> >>
> >> You may be confused about what you were supposed to do. A
> >> combo box does not have a record source, they do have a
> >> RowSource. Either way, you should not mess with the combo
> >> box.
> >
> >> Hannah wrote:
> >> >Thanks for such a speedy reply! I tried doing what you said but I got a
> >> >message saying "Enter Parameter Value" and then the bit of code you gave me.
> >> >I've checked I entered it correctly and in the right place but beyond that
> >> >I'm a bit stumped about what's causing the problem. Could it be clashing with
> >> >other code? I previously had the cboHarbour record source as the Harbour
> >> >table.
> .
>
From: Hannah on
I take back my first attempt at trying to work out why no records show! What
happens when I choose something in cboHarbour is that I get the the message
"Run-time error '3021': no current record" I then get taken to my code for
sfrmHarbours which is:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[HID] = " & Str(Me![cboHarbour])
Me.Bookmark = rs.Bookmark

End Sub

The section of the code highlighted is the bookmark property. How can I get
around this?! Thank you for all your help again!

"Marshall Barton" wrote:

> Does "go wrong" still mean you are being prompted for
> something like:
>
> Forms!formname.subformcontrolname.Form.comboname
>
> If so and you have double checked the spelling of
> everything, then I suspect that you have used the name of
> the form object displayed in the subform control instead of
> the name of the subform control on the main form. The
> subform control on the main form is often named the same as
> the form object it displays, but they can be different.
> --
> Marsh
> MVP [MS Access]
>
>
> Hannah wrote:
> >Thanks Marshall. I did change the subform record source and I changed it
> >using the names of my own controls in place of your dummies so really not
> >sure what's making it go wrong? Any ideas?
> >
> >"Marshall Barton" wrote:
> >> The fact that you were prompted to enter somethng means that
> >> you misspelled whatever the prompt box was asking for.
> >>
> >> You may be confused about what you were supposed to do. A
> >> combo box does not have a record source, they do have a
> >> RowSource. Either way, you should not mess with the combo
> >> box.
> >
> >> Hannah wrote:
> >> >Thanks for such a speedy reply! I tried doing what you said but I got a
> >> >message saying "Enter Parameter Value" and then the bit of code you gave me.
> >> >I've checked I entered it correctly and in the right place but beyond that
> >> >I'm a bit stumped about what's causing the problem. Could it be clashing with
> >> >other code? I previously had the cboHarbour record source as the Harbour
> >> >table.
> .
>
From: Marshall Barton on
Hannah wrote:

>I take back my first attempt at trying to work out why no records show! What
>happens when I choose something in cboHarbour is that I get the the message
>"Run-time error '3021': no current record" I then get taken to my code for
>sfrmHarbours which is:
>
>Private Sub cboHarbour_AfterUpdate()
> ' Find the record that matches the control.
> Dim rs As Object
>
> Set rs = Me.Recordset.Clone
> rs.FindFirst "[HID] = " & Str(Me![cboHarbour])
> Me.Bookmark = rs.Bookmark
>
>End Sub


No current record means that the recordset is empty,
something tried to move the current record off the end of
the recordset or, in your case, the find operation did not
find a match.

Your code below has some minor issue and a major problem.
The big one is that you need to check if the FindFirst found
a record or not. If it did not find a match, you can not
set the form's bookmark as if it did find a match.

I would code that more like this air code:

Private Sub cboHarbour_AfterUpdate()
' Find the record that matches the control.
With Me.RecordsetClone
If .RecordCount > 0 Then
.FindFirst "HID = " & Me!cboHarbour
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else
MsgBox "No matching record"
End If
Else
MsgBox "There are no records to seach"
End If
End With
End Sub

Note that HID must be a number type field in the table, If
it were a Text field, it would be:
.FindFirst "HID = """ & Me!cboHarbour & """"

If you get the no matching record message, check to make
sure the combo box's BoundColumn corresponds to the HID
field in its RowSource.

--
Marsh
MVP [MS Access]
First  |  Prev  | 
Pages: 1 2
Prev: Form filter problem
Next: Format Memo field