I dont know if this has already been posted on here, but i am really stuck
and have been searching for ages for an answer.
I have got a form where a customer would enter their membership number into
a textbox, when they hit the find button, i want the sql to find the record
that matches in the members table and put the results in the subform which
has specific textboxes for the different fields.

the code i have done is:

Dim db As Database
Dim recSetMember As Recordset
Dim sqlStatement As String


sqlStatement = "SELECT * from members where [members].[Member No] =
[Member_No]"

Set db = CurrentDb()
Set recSetMember = db.OpenRecordset(sqlStatement, , dbConsistent)


[members details].Visible = True
[recSetMember]![Title] = [members details]![Title]

at the moment it is throwing up an error when it gets to the :
Set recSetMember = db.OpenRecordset(sqlStatement, , dbConsistent)

please help.
Jo

Is this an unbound form?

Here is the correct way to do this with a text box (a combo box is better
for this sort of thing). Note the control you use for this search should be
an unbound control. If you use a bound control, as soon as you enter a value
in it, it will update the recordset field with the new value, which is
usually not what you want. What I usually do is use a combo box for the
search and a text box for the bound control. Then when you do the search and
get a match, you position the form to the selected record.

Private Sub cboMember_AfterUpdate()
Dim rst As Recordset

Set rst = Me.RecordsetClone
With rst
.FindFirst = "[MemberId] = " & Me.cboMember
If .NoMatch Then
MsgBox Me.cboMember & " Was not found", vbExclamation
Else
Me.Bookmark = .Bookmark
End If
End With
Set rst = Nothing
End Sub

"Jo" wrote:

> I dont know if this has already been posted on here, but i am really stuck
> and have been searching for ages for an answer.
> I have got a form where a customer would enter their membership number into
> a textbox, when they hit the find button, i want the sql to find the record
> that matches in the members table and put the results in the subform which
> has specific textboxes for the different fields.
>
> the code i have done is:
>
> Dim db As Database
> Dim recSetMember As Recordset
> Dim sqlStatement As String
>
>
> sqlStatement = "SELECT * from members where [members].[Member No] =
> [Member_No]"
>
> Set db = CurrentDb()
> Set recSetMember = db.OpenRecordset(sqlStatement, , dbConsistent)
>
>
> [members details].Visible = True
> [recSetMember]![Title] = [members details]![Title]
>
> at the moment it is throwing up an error when it gets to the :
> Set recSetMember = db.OpenRecordset(sqlStatement, , dbConsistent)
>
> please help.
> Jo

Jo wrote:

>I dont know if this has already been posted on here, but i am really stuck
>and have been searching for ages for an answer.
>I have got a form where a customer would enter their membership number into
>a textbox, when they hit the find button, i want the sql to find the record
>that matches in the members table and put the results in the subform which
>has specific textboxes for the different fields.
>
>the code i have done is:
>
>Dim db As Database
> Dim recSetMember As Recordset
> Dim sqlStatement As String
>
>
> sqlStatement = "SELECT * from members where [members].[Member No] =
>[Member_No]"
>
> Set db = CurrentDb()
> Set recSetMember = db.OpenRecordset(sqlStatement, , dbConsistent)
>
>
> [members details].Visible = True
> [recSetMember]![Title] = [members details]![Title]
>
>at the moment it is throwing up an error when it gets to the :
>Set recSetMember = db.OpenRecordset(sqlStatement, , dbConsistent)


You should do this kind of synchronization bia the Link
Master/Child Fields properties of the subform control, no
code needed.

I think you want the Link Master would be the name of the
member number text box and the Link Child property would be
the [Member No] field.

--
Marsh
MVP [MS Access]

what i should of said before is that there are about 5 textboxes that i want
the 5 fields of the table to go into, not including the membership number,
that is just a field to search for the relevant row in the table.
it easier to understand if you can see the actual form.
the membership number is on the main form, and the subform is where all the
rest of the data from the table gets inputted.
jo

"Klatuu" wrote:

> Is this an unbound form?
>
> Here is the correct way to do this with a text box (a combo box is better
> for this sort of thing). Note the control you use for this search should be
> an unbound control. If you use a bound control, as soon as you enter a value
> in it, it will update the recordset field with the new value, which is
> usually not what you want. What I usually do is use a combo box for the
> search and a text box for the bound control. Then when you do the search and
> get a match, you position the form to the selected record.
>
> Private Sub cboMember_AfterUpdate()
> Dim rst As Recordset
>
> Set rst = Me.RecordsetClone
> With rst
> .FindFirst = "[MemberId] = " & Me.cboMember
> If .NoMatch Then
> MsgBox Me.cboMember & " Was not found", vbExclamation
> Else
> Me.Bookmark = .Bookmark
> End If
> End With
> Set rst = Nothing
> End Sub
>
> "Jo" wrote:
>
> > I dont know if this has already been posted on here, but i am really stuck
> > and have been searching for ages for an answer.
> > I have got a form where a customer would enter their membership number into
> > a textbox, when they hit the find button, i want the sql to find the record
> > that matches in the members table and put the results in the subform which
> > has specific textboxes for the different fields.
> >
> > the code i have done is:
> >
> > Dim db As Database
> > Dim recSetMember As Recordset
> > Dim sqlStatement As String
> >
> >
> > sqlStatement = "SELECT * from members where [members].[Member No] =
> > [Member_No]"
> >
> > Set db = CurrentDb()
> > Set recSetMember = db.OpenRecordset(sqlStatement, , dbConsistent)
> >
> >
> > [members details].Visible = True
> > [recSetMember]![Title] = [members details]![Title]
> >
> > at the moment it is throwing up an error when it gets to the :
> > Set recSetMember = db.OpenRecordset(sqlStatement, , dbConsistent)
> >
> > please help.
> > Jo

ok i have made it an unbound combo box where they search for the membership
number. how do i get it to display the other 5 bits of data in the subform?
preferably in their own textboxes.

"Marshall Barton" wrote:

> Jo wrote:
>
> >I dont know if this has already been posted on here, but i am really stuck
> >and have been searching for ages for an answer.
> >I have got a form where a customer would enter their membership number into
> >a textbox, when they hit the find button, i want the sql to find the record
> >that matches in the members table and put the results in the subform which
> >has specific textboxes for the different fields.
> >
> >the code i have done is:
> >
> >Dim db As Database
> > Dim recSetMember As Recordset
> > Dim sqlStatement As String
> >
> >
> > sqlStatement = "SELECT * from members where [members].[Member No] =
> >[Member_No]"
> >
> > Set db = CurrentDb()
> > Set recSetMember = db.OpenRecordset(sqlStatement, , dbConsistent)
> >
> >
> > [members details].Visible = True
> > [recSetMember]![Title] = [members details]![Title]
> >
> >at the moment it is throwing up an error when it gets to the :
> >Set recSetMember = db.OpenRecordset(sqlStatement, , dbConsistent)
>
>
> You should do this kind of synchronization bia the Link
> Master/Child Fields properties of the subform control, no
> code needed.
>
> I think you want the Link Master would be the name of the
> member number text box and the Link Child property would be
> the [Member No] field.
>
> --
> Marsh
> MVP [MS Access]
>

If I understand what you want, just set the Link Master
proeprty to the text or combo box and the Link Child field
to the [Member No] field. As long as the subform is bound
to the table, that's all it should require.
--
Marsh
MVP [MS Access]


Jo wrote:

>ok i have made it an unbound combo box where they search for the membership
>number. how do i get it to display the other 5 bits of data in the subform?
>preferably in their own textboxes.
>
>"Marshall Barton" wrote:
>
>> Jo wrote:
>>
>> >I dont know if this has already been posted on here, but i am really stuck
>> >and have been searching for ages for an answer.
>> >I have got a form where a customer would enter their membership number into
>> >a textbox, when they hit the find button, i want the sql to find the record
>> >that matches in the members table and put the results in the subform which
>> >has specific textboxes for the different fields.
>> >
>> >the code i have done is:
>> >
>> >Dim db As Database
>> > Dim recSetMember As Recordset
>> > Dim sqlStatement As String
>> >
>> >
>> > sqlStatement = "SELECT * from members where [members].[Member No] =
>> >[Member_No]"
>> >
>> > Set db = CurrentDb()
>> > Set recSetMember = db.OpenRecordset(sqlStatement, , dbConsistent)
>> >
>> >
>> > [members details].Visible = True
>> > [recSetMember]![Title] = [members details]![Title]
>> >
>> >at the moment it is throwing up an error when it gets to the :
>> >Set recSetMember = db.OpenRecordset(sqlStatement, , dbConsistent)
>>
>>
>> You should do this kind of synchronization bia the Link
>> Master/Child Fields properties of the subform control, no
>> code needed.
>>
>> I think you want the Link Master would be the name of the
>> member number text box and the Link Child property would be
>> the [Member No] field.