DAO Troubleshooting. 1

[AppStaff]

Dim db As Database
Dim rstDIS As DAO.Recordset
Dim rstTL As DAO.Recordset
Dim rstCR As DAO.Recordset
Dim rstDef As DAO.Recordset

Dim strQ As String
Dim strQ2 As String
Dim strTL As String
Dim IDN As Integer

Set db = CurrentDb



' Define Selection.
strQ = "qry_SEL_DCEXP_N"
strQ2 = Left(strQ, Len(strQ) - 2)
Set rstTL = db.OpenRecordset(strQ)
Set rstDIS = db.OpenRecordset("tblDiscrepancies")
Set rstDef = db.OpenRecordset("tblDiscrepancyDetail")
Set rstCR = db.OpenRecordset("tblCRDetail")

'write records to Discrepancies table

Do While Not rstTL.EOF

'set variable for team_lead
strTL = rstTL!Team_Lead
IDN = DMax("DefectNumber", "tblDiscrepancies") + 1
Debug.Print "After loop name"; strTL; "DefectNumber= "; IDN
rstDIS.AddNew
rstDIS!DefectNumber = IDN
IDN = rstDIS!DefectNumber
rstDIS!Originator = Me.cmbOrig
rstDIS!AdminName = Me.cmbAdmin
rstDIS!Owner = strTL
rstDIS!SubmitDate = Now()
rstDIS!ActionType = "Defect"
rstDIS!Analysis = "Current Express Phase Missing from these open projects."
rstDIS!Request = "TL to update RTM."
rstDIS.Update
Debug.Print "DIS Def No= "; rstDIS!DefectNumber; " DIS Owner= "; rstDIS!Owner

'Write records to Discrepancy Detail
rstDef.AddNew
Debug.Print "Defect Number Strt= "; IDN
rstDef!DefectNumber = IDN
rstDef!ItemNumber = IIf(IsNull(DMax("ItemNumber", "tblDiscrepancyDetail", "DefectNumber=" & IDN & "")), 1, DMax("ItemNumber", "tblDiscrepancyDetail", "DefectNumber=" & IDN & ""))
rstDef!DefectID = "7"
rstDef!Quantity = DCount("Team_Lead", strQ2, "Team_Lead='" & strTL & "'")
rstDef.Update
Debug.Print "DEF Def No= "; rstDef!DefectNumber; " DEF Item no= "; rstDef!ItemNumber; " DEF Qty= "; rstDef!Quantity

'Write Records to CR Detail

rstTL.MoveNext
rstDIS.MoveNext
rstDef.MoveNext
Loop




rstTL.Close
rstDIS.Close

Set rstTL = Nothing
Set rstDIS = Nothing

Basically what I'm doing is creating 3 recordsets. The first has a list of names. I populate the 2nd with a record for each name in the first and assign a unique id for each record. I then Attempt to create a new record in a table related by the unique id to the 2nd using the 3rd recordset.

The Problem:

The problem with this code is with the related table recordset (3). For some reason its setting my id as 1 and ignoring the variable. This happens to be the first record in the table though I'm not certain if its looking this up or just starting at 1. When I debug.print the variable I'm assigning it is the value I want... the same id from the 2nd recordset. I'm not very good with DAO and am having difficulty determining what I'm doing wrong. Likely I'm not managing the recordsets correctly. Can anyone see why the recordset is not using my variable as I specify. Any suggestions or code improvements are welcome.

 

[jbpez]

Try the following:

    Dim rstDIS As DAO.Recordset
    Dim rstTL As DAO.Recordset
    Dim rstDef As DAO.Recordset
    Dim strQ As String
    Dim strQ2 As String
    Dim strTL As String
    Dim IDN As Integer
      
    ' Define Selection.
    strQ = "qry_SEL_DCEXP_N"
    strQ2 = Left(strQ, Len(strQ) - 2)
    Set rstTL = CurrentDb.OpenRecordset(strQ, dbOpenSnapshot)
    Set rstDIS = CurrentDb.OpenRecordset("tblDiscrepancies", dbOpenTable)
    Set rstDef = CurrentDb.OpenRecordset("tblDiscrepancyDetail", dbOpenTable)
    
    IDN = DMax("DefectNumber", "tblDiscrepancies") + 1
    'write records to Discrepancies table
    Do Until rstTL.EOF
        
        'set variable for team_lead
        strTL = rstTL("Team_Lead").Value

        rstDIS.AddNew
        rstDIS("DefectNumber").Value = IDN
        rstDIS("Originator").Value = Me.cmbOrig.Value
        rstDIS("AdminName").Value = Me.cmbAdmin.Value
        rstDIS("Owner").Value = strTL
        rstDIS("SubmitDate").Value = Now()
        rstDIS("ActionType").Value = "Defect"
        rstDIS("Analysis").Value = "Current Express Phase Missing from these open projects."
        rstDIS("Request").Value = "TL to update RTM."
        rstDIS.Update
       
        'Write records to Discrepancy Detail
        rstDef.AddNew
        rstDef("DefectNumber").Value = IDN
        rstDef("ItemNumber").Value = IIf(IsNull(DMax("ItemNumber", "tblDiscrepancyDetail", "DefectNumber=" & IDN & "")), 1, DMax("ItemNumber", "tblDiscrepancyDetail", "DefectNumber=" & IDN & ""))
        rstDef("DefectID").Value = "7"
        rstDef("Quantity").Value = DCount("Team_Lead", strQ2, "Team_Lead='" & strTL & "'")
        rstDef.Update
        
    IDN = IDN + 1
    rstTL.MoveNext
    Loop
    
    rstDef.Close
    rstDIS.Close
    rstTL.Close
    Set rstDef = Nothing
    Set rstDIS = Nothing
    Set rstTL = Nothing

John Borges

 

[AppStaff]

Thanks for everyones responses. I was really struggling with this I think because of the size so I decided to attempt to separate it into 3 functions. Unfortunately I have a few more questions.

Call LoadDIS(RefID)
Call LoadDET(strTL, IDN, RefID)
Call LoadCR(strTL, IDN, RefID)

1. If i define a variable strQ in my LoadDET() how can I pass that value to my LoadCR() or do I need to redefine it each time or do I need to define it in the sub procedure and pass it to each function that way? If the first function runs will all the variable values still be in memory for the second and third functions unless I reset them?


2. When I'm writing to a recordset an .addnew is required. however, i'm not quite sure how to handle a situation when I'm just reading from it. Do I need to move to the first record or .BOF or can I just start setting values from the recordset?