Populate form from list box selection

[Bird Byte]

I have a list box on form 1 that populates when the form opens. When someone
clicks on an item, form 2 opens with text boxes filled with info dependent on
the record that was selected on form 1.

It only works for the first record. If any other record is selected error
2501 is generated (The OpenForm action was cancelled) behind form 1 and error
3164 (Field cannot be updated) behind form 2. MemberID is is an auto number
primary key in the Member table.

Code:

Form 1:
Private Sub Form_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim stSQL As String

stSQL = "SELECT Members.MemberID AS ID, Members.LName & ', ' +
Members!FName AS Name,iif(Contact![BestContact]='Home', [PhHome], [PhWork])AS
Phone FROM Members, Contact WHERE Members.MemberID = Contact.MemberID ORDER
BY Members.LName"

Set db = CurrentDb
Set rs = db.OpenRecordset(stSQL, dbOpenDynaset)

Me.lstMembers.RowSource = stSQL
Me.lstMembers.Requery

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

Private Sub lstMembers_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmMember", , , "MemberID= '" & Me.lstMembers & "'", , ,
Me.lstMembers
End Sub

Form 2:
Private Sub Form_Load()
'If IsNull(Me.OpenArgs) Then
'programming Error
'Else
Me.MemberID = Me.OpenArgs
'End If
End Sub

I've tried with code behind form 2 and no code - it happens either way.

Thanks very much for any ideas.

 

[NetworkTrade]

sounds like form1 and 2 are sourced on the same table;

would you consider merging these forms? ....put the listbox into the header
of form2 - and the wizard will launch with an option to find the record of
what is selected.....

then just use form2 - and upon selection - all the data will appear...

 

[Bird Byte]

Thanks for the idea, but I unfortunately can't incorporate both forms onto
one (if I'm following you right - a form with a subform?). Also, the info is
coming from 2 tables (Members and Contact) for now, and more table may get
added in as the project grows.

I haven't seen any wizards for this (I'm using Access '03 if that makes a
difference). I'd like to do it with code, since that makes tweaking easier in
the future.

Thanks again.

 

[NetworkTrade]

well then you should focus on the opening logic of form 2. with no code it
should open to all records.... that is the first step as a sanity check.
one has to get this basic step working ok....

and then tweak the code so form 2 opens to the record of form1's selected
value...
--
NTC

Thanks for the idea, but I unfortunately can't incorporate both forms onto
one (if I'm following you right - a form with a subform?). Also, the info is
coming from 2 tables (Members and Contact) for now, and more table may get
added in as the project grows.

I haven't seen any wizards for this (I'm using Access '03 if that makes a
difference). I'd like to do it with code, since that makes tweaking easier in
the future.

Thanks again.

 

[Bird Byte]

I got it working by changing the DoCmd.OpenForm string from:

Private Sub lstMembers_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmMember", , , "MemberID= '" & Me.lstMembers & "'", , ,
Me.lstMembers
End Sub

to:

Private Sub lstMembers_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmMember", acNormal, , "MemberID= " & Me.lstMembers
End Sub

Not sure why. Anybody know any resources where I can learn more about using
double and single quotation marks in sql statements?

well then you should focus on the opening logic of form 2. with no code it
should open to all records.... that is the first step as a sanity check.
one has to get this basic step working ok....

and then tweak the code so form 2 opens to the record of form1's selected
value...