Need Help - display multiple records

[Billie]

I need some help.... I have inherited a database and have
been asked to make some changes (in this case allowing
duplicate records). The database was setup so that the
form displays empty, they enter a couple of key fields and
then they click a find button. Behind the find button is
code to query for the records and display them. It's
pulling the multiple records and I've got it to loop
through the fields in the recordset but I can't seem to
create a new record on the form it just overwrites the
same record over and over. Can you tell me what how to
create a new record on the form? Any help you can giveis
greatly appreciated! Here is the code that I have behind
the find button:
Dim dbs As Database, rst As Recordset
Dim sSQL As String

sSQL = "My SQL that is returning multiple records with
multiple fields"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL)

If rst.RecordCount > 0 Then
With rst
Do While Not .EOF
Me.Cmd_Find_Record.Enabled = True
Me.Cmd_Edit_Record.Enabled = True
Me.Cmd_Save_Record.Enabled = False
Me.Cmd_Undo_Changes.Enabled = False

With Me
!cboSystem = rst.Fields
("Plant_System_ID_No").Value
'All other fields like cboSystem
End With
.MoveNext
Loop
End With
Else
MsgBox ("No record found.")
Me.Cmd_Edit_Record.Enabled = False
End If

rst.Close
Set dbs = Nothing
Set rst = Nothing

Exit_Cmd_Find_Record_Click:
Exit Sub

Err_Cmd_Find_Record_Click:
MsgBox Err.Description
Resume Exit_Cmd_Find_Record_Click

 

[Guest]

I need some help.... I have inherited a database and have
been asked to make some changes (in this case allowing
duplicate records). The database was setup so that the
form displays empty, they enter a couple of key fields and
then they click a find button. Behind the find button is
code to query for the records and display them. It's
pulling the multiple records and I've got it to loop
through the fields in the recordset but I can't seem to
create a new record on the form it just overwrites the
same record over and over. Can you tell me what how to
create a new record on the form? Any help you can giveis
greatly appreciated! Here is the code that I have behind
the find button:
Dim dbs As Database, rst As Recordset
Dim sSQL As String

sSQL = "My SQL that is returning multiple records with
multiple fields"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL)

If rst.RecordCount > 0 Then
With rst
Do While Not .EOF
Me.Cmd_Find_Record.Enabled = True
Me.Cmd_Edit_Record.Enabled = True
Me.Cmd_Save_Record.Enabled = False
Me.Cmd_Undo_Changes.Enabled = False

With Me
!cboSystem = rst.Fields
("Plant_System_ID_No").Value
'All other fields like cboSystem
End With
.MoveNext
Loop
End With
Else
MsgBox ("No record found.")
Me.Cmd_Edit_Record.Enabled = False
End If

rst.Close
Set dbs = Nothing
Set rst = Nothing

Exit_Cmd_Find_Record_Click:
Exit Sub

Err_Cmd_Find_Record_Click:
MsgBox Err.Description
Resume Exit_Cmd_Find_Record_Click

Hi Billie,

I'm not a SQL Wizard <grin> (I love puns!), but I don't see anything in your
code to tell it to create a new record.

It appears that you are using DAO, so try adding two lines to you code:

'------- SNIP ----------------------
With Me
' add next line - creates new record ************
.AddNew

!cboSystem = rst.Fields("Plant_System_ID_No").Value
'All other fields like cboSystem
' another field
' yet more fields
' add next line - saves new record *************
.Update

End With

'------- SNIP ----------------------

Remember, you are working with *two* recordsets.......

HTH
Steve

 

[Billie]

Steve, I tried the .AddNew and .Update and I got "Compile
Error: Method or data member not found". I know that when
they created this they did not put a table as the source
for the form, could that be the problem? Any other ideas?
Thanks...Billie