Inserting records to a table using query and form data

[Amadea]

I've searched the archives and recent posts, but have not found exactly the solution I need. Your help will be much appreciated!

I need to add new or update a record in a table. In the code below, the Else part works. It's the first part I just can't get right. What I'm trying to say is for each CRN in qryXLst (and I've tried For Each but can't get that right), add new record using the CRN from qryXLst and info from current form.

'Instructor #2
If Me!cboInstr2 <> "" And Me!cboInstr2 <> "STAFF" Then
rs.FindFirst "Sess = '02' "
If rs.NoMatch Then
Dim qryXLst As String
qryXLst = "SELECT [AllSections].[CRN] " & _
"FROM AllSections " & _
"WHERE [AllSections].[XLst]= '" & Me!XLst & "' ;"
DoCmd.OpenQuery qryXLst

Do While Not rs.EOF
rs.AddNew
rs!CRN = qryXLst
rs!Sess = "02"
rs!InstrName = Me!cboInstr2
rs!LName = Me!cboInstr2.Column(1)
rs!FName = Me!cboInstr2.Column(2)
rs!PInd = ""
rs!Sess = "02"
rs!DivNum = Me!txtDivNum
rs!XLst = Me!XLst
rs.MoveNext
Loop

Else
Do While Not rs.EOF
rs.Edit
rs!InstrName = Me!cboInstr2
rs!LName = Me!cboInstr2.Column(1)
rs!FName = Me!cboInstr2.Column(2)
rs!PInd = ""
rs!Sess = "02"
rs!DivNum = Me!txtDivNum
rs.Update
rs.MoveNext
Loop
End If

 

[PWise]

try

rs!CRN = qryXLst(0)

 

[joelflorendo]

hmm...it's not real clear what you're trying to do. What is your table structure? It looks like you're using a DAO recordset(?) but you're not actually opening the recordset anywhere. There should be a line:

rs.OpenRecordset(qryXLst)

somewhere, which I'm guessing is what you meant by the line:

Docmd.OpenQuery(qryXLst)

Maybe give a little more detail on what you're trying to accomplish and we can better point you in the right direction.

 

[Amadea]

It’s kind of similar to the question on the list from Jun 27, except I’m not adding unnecessary info (I don’t think . I built the database when data was structured differently. Since then, the data structure was significantly changed and I’m just now catching up in revising the database to work efficiently with the new data.

The table InstrName includes the instructor name for every course offered (CRNs are the unique identifiers). When the course (CRN) has only one instructor, the Sess is 01. When there is more than one instructor for a course (CRN), instructor #1 is 01, instructor #2 is 02, etc. An 01 record exists for every course, whether the instructor name is someone or STAFF. What complicates this is that two or more courses can be cross listed and the cross list group has a unique alpha/numeric identifier. Which circumstance exists is determined by the first few lines of the code.

' Open selected records from InstrName table matching current form CRN or XLst and update instructor name table

Dim db As DAO.Database
Set db = CurrentDb

If IsNull(Me!XLst) Or Me!XLst = "" Then
Set rs = db.OpenRecordset("SELECT * FROM InstrName WHERE CRN = '" & Me!CRN & "' ", dbOpenDynaset)
Else
Set rs = db.OpenRecordset("SELECT * FROM InstrName WHERE InstrName.XLst = '" & Me!XLst & "' ", dbOpenDynaset)
End If

When there is only one instructor, I think the following is working okay to change instructor 01 on each CRN in the cross list group.

'Instructor #1 - Primary
If Me!cboInstr1 <> "" Then
rs.FindFirst "Sess = '01' "
Do While Not rs.EOF
rs.Edit
rs!InstrName = Me!cboInstr1
rs!LName = Me!cboInstr1.Column(1)
rs!FName = Me!cboInstr1.Column(2)
rs.Update
rs.MoveNext
Loop

ElseIf IsNull(Me!cboInstr1) And Me!txtInstr1Lname <> "" Then
rs.FindFirst "Sess = '01' "
Do While Not rs.EOF
rs.Edit
rs!InstrName = Me!txtInstr1Lname & ", " & Me!txtInstr1Fname
rs!LName = Me!txtInstr1Lname
rs!FName = Me!txtInstr1Fname
rs!PInd = "Y"
rs!Sess = "01"
rs!DivNum = Me!txtDivNum
rs.Update
rs.MoveNext
Loop

ElseIf IsNull(Me!cboInstr1) And Me!txtInstr1Lname = "" Then
'rs.Close
'db.Close
End If

What I’m trying to say in VBA is add instructor #2 for all CRNs in the cross list group. First look for 02 records in InstrName for the CRN on this form. If 02 doesn’t exist, then add a new record for each CRN in the cross list group. The cross list identifier is available from the form in Me!XLst and the CRNs in the group are displayed on the form in a list box populated by a query. It seems logical to interrate through the CRNs in the list box and add a record for each CRN. However, I can’t seem to get that down in VBA.

Does this help??

 

[joelflorendo]

If I'm understanding correctly, you just want to add a record to the table based on the CRN values in the list box.

To loop through the CRN values in the list box use:

for i=0 to Me!XLst.ListCount - 1
'add new record
rs.AddNew
rs!CRN = Me!Xlst.ItemData(i)
'add the rest of the record data

rs.Update
next i

 

[Amadea]

Thank you. Yes, you understand it completely. Here's what I have, but it's not adding the record. My list box is named lstCombSect and it has 4 columns of data, i.e., CRN, Subj, Num, Xlst. Do I need to specify which column of data?

With 'Private i as Integer' in the Declarations.

'Instructor #2
If Me!cboInstr2 <> "" And Me!cboInstr2 <> "STAFF" Then
rs.FindFirst "Sess = '02' "
If rs.NoMatch Then
For i = 0 To Me!lstCombSect.ListCount - 1
'add new record
rs.AddNew
rs!CRN = Me!lstCombSect.ItemData(i)
'add the rest of the record data
rs!InstrName = Me!cboInstr2
rs!LName = Me!cboInstr2.Column(1)
rs!FName = Me!cboInstr2.Column(2)
rs!PInd = ""
rs!Sess = "02"
rs!DivNum = Me!txtDivNum
rs!XLst = Me!XLst
rs.Update
Next i
Else
Do While Not rs.EOF
rs.Edit
rs!InstrName = Me!cboInstr2
rs!LName = Me!cboInstr2.Column(1)
rs!FName = Me!cboInstr2.Column(2)
rs!PInd = ""
rs!Sess = "02"
rs!DivNum = Me!txtDivNum
rs!XLst = Me!XLst
rs.Update
rs.MoveNext
Loop
End If

 

[joelflorendo]

It depends on which column is the bound column for the list box (i.e. which column contains the actual data that is saved to the table). If the CRN column is bound, then you won't need to specify the column, but if not you would need to use:

me!lstCombSect.Column(<column # of CRN>,i)

to reference it. Column numbers start from 0.

Is it giving you an error message when it tries to add the record? From the code you supplied, it should at least add a new record even if it's not storing the correct data in the table.

 

[Amadea]

Yes, CRN is the first column and bound column. So I shouln't have to worry about that.

My test data was a little scewey so it looked like nothing was working. Once I cleared up the extraneous stuff "created" during previous tests, it seems to work perfectly now.

Thank you all SO MUCH for your help. One more hurdle cleared!!!

Nathalie