Solved Item cannot be found in the collection corresponding to the request.....

[Chief]

Hello,

I have a Table "JobTypesT" and another Table "JobInfoT"
JobInfoT holds all the information of the Job.
JobTypesT holds a certain amount of days depending on the job type and then the process type.

I am trying to achieve, look at one date then get the days from JobTypesT where they match and retrieve the number.
Getting some errors in the following code. (Item cannot be found in the collection corresponding to the request.....)
At the rs.Open sql, con, 1
In this instance should say 18

Function GetDays(sVal)

    Dim db As Database
    Dim sql As String, sVal1 As String, sVal2 As String
    Dim con As Object
    Dim rs As Object
    
    Set con = Application.CurrentProject.Connection
    Set db = CurrentDb
    
    sql = "SELECT " & sVal & " FROM JobTypesT WHERE JobTypesID = " & Me.CboJobTypeID
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sql, con, 1
    
    dValue = rs(0)
    rs.Close
    Set rs = Nothing
  
    GetDays = dValue
End Function

I believe (I think) the error is coming from this code

'------------------------------------------------------------
Private Sub BtnUpdate_Click()
    Dim db As Database
    Dim sql As String, sVal1 As String, sVal2 As String
    Dim con As Object
    Dim rs As Object
    Dim dDate As Date
    Dim iLoop As Integer, iDays As Integer ', iAcct As Integer

    Set con = Application.CurrentProject.Connection
    Set db = CurrentDb

'    CboCustomerID.SetFocus
'    If CboCustomerID.Text = "" Then
'        MsgBox "Please select a customer from the list", vbInformation, "Select Customer"
'        Exit Sub
'    End If

    CboJobTypeID.SetFocus

    If CboJobTypeID.Text = "" And Nz(Me.Lead_Date, "") = "" Then
        Exit Sub
    End If

    If CboJobTypeID.Text = "" Then
        MsgBox "Please select a Job Type from the list", vbInformation, "Select Job Type"
        Exit Sub
    End If
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    If Lead_Date.Value & "" = "" Then
        Lead_Date.Value = Now()
    End If
    
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Me.Refresh

'    sql = "SELECT systemdays FROM customer INNER JOIN PaymentT ON PaymentT.PaymentID = CustomerT.PaymentID WHERE CustomerID = " & Me.CboCustomerID
'    Set rs = CreateObject("ADODB.Recordset")
'    rs.Open sql, con, 1
'
'    If rs.EOF Or rs.BOF Then
'        iDays = 4
'    Else
'        iDays = rs(0)
'        rs.Close
'        Set rs = Nothing
'    End If

        sql = "UPDATE JobTypesT SET IFA_Due = #" & AddDays(GetDays("IFA_Due")) & "#,SampleSubm_Due = #" & AddDays(GetDays("SampleSubm_Due")) & _
        "#,IFC_Due = #" & AddDays(GetDays("IFC_Due")) & "#,SetOutDue = #" & AddDays(GetDays("SetOutDue")) & _
        "#,CarcassCut_Due = #" & AddDays(GetDays("CarcassCut_Due")) & "#,CarcassEdge_Due = #" & AddDays(GetDays("CarcassEdge_Due")) & _
        "#,PFBCut_Due = #" & AddDays(GetDays("PFBCut_Due")) & "#,PFBEdge_Due = #" & AddDays(GetDays("PFBEdge_Due")) & _
        "#,WhiteSatinCut_Due = #" & AddDays(GetDays("WhiteSatinCut_Due")) & "#,TwoPakPartsOut_Due = #" & AddDays(GetDays("TwoPakPartsOut_Due")) & _
        "#,PickHW_Due = #" & AddDays(GetDays("PickHW_Due")) & "#,HingeDrill_Due = #" & AddDays(GetDays("HingeDrill_Due")) & _
        "#,MachineShop_Due = #" & AddDays(GetDays("MachineShop_Due")) & "#,DrawerAss_Due = #" & AddDays(GetDays("DrawerAss_Due")) & _
        "#,AssemblyDue = #" & AddDays(GetDays("AssemblyDue")) & "#,TwoPakUnderC_Due = #" & AddDays(GetDays("TwoPakUnderC_Due")) & _
        "#,TwoPakPaint_Due = #" & AddDays(GetDays("TwoPakPaint_Due")) & "#,WrapQC_Due = #" & AddDays(GetDays("WrapQC_Due")) & _
        "#,Delivery_Due = #" & AddDays(GetDays("Delivery_Due")) & "# WHERE JobID = " & Me.JobID
        
        sql = "UPDATE JobInfoT SET Lead_Date = #" & AddOrderDays(GetDays("Lead_Date")) & _
        "#,SET IFA_Due = #" & AddOrderDays(GetDays("SET IFA_Due")) & "#,SampleSubm_Due = #" & AddOrderDays(GetDays("SampleSubm_Due")) & _
        "#,IFC_Due = #" & AddOrderDays(GetDays("IFC_Due")) & "#,SetOutDue = #" & AddOrderDays(GetDays("SetOutDue")) & _
        "#,CarcassCut_Due = #" & AddOrderDays(GetDays("CarcassCut_Due")) & "#,CarcassEdge_Due = #" & AddOrderDays(GetDays("CarcassEdge_Due")) & _
        "#,PFBCut_Due = #" & AddOrderDays(GetDays("PFBCut_Due")) & "#,PFBEdge_Due = #" & AddOrderDays(GetDays("PFBEdge_Due")) & _
        "#,WhiteSatinCut_Due = #" & AddOrderDays(GetDays("WhiteSatinCut_Due")) & "#,TwoPakPartsOut_Due = #" & AddOrderDays(GetDays("TwoPakPartsOut_Due")) & _
        "#,PickHW_Due = #" & AddOrderDays(GetDays("PickHW_Due")) & "#,HingeDrill_Due = #" & AddOrderDays(GetDays("HingeDrill_Due")) & _
        "#,MachineShop_Due = #" & AddOrderDays(GetDays("MachineShop_Due")) & "#,DrawerAss_Due = #" & AddOrderDays(GetDays("DrawerAss_Due")) & _
        "#,AssemblyDue = #" & AddOrderDays(GetDays("AssemblyDue")) & "#,TwoPakUnderC_Due = #" & AddOrderDays(GetDays("TwoPakUnderC_Due")) & _
        "#,TwoPakPaint_Due = #" & AddOrderDays(GetDays("TwoPakPaint_Due")) & "#,WrapQC_Due = #" & AddOrderDays(GetDays("WrapQC_Due")) & _
        "#,Delivery_Due = #" & AddOrderDays(GetDays("Delivery_Due")) & "# WHERE JobID = " & Me.JobID

    db.Execute sql
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Me.Refresh
    
End Sub

Is it because my fields are named the same?

Thank you

 

[The_Doc_Man]

In this context, what is the value of the call argument sVal and do you have a field of that name?

In this sequence

    sql = "SELECT " & sVal & " FROM JobTypesT WHERE JobTypesID = " & Me.CboJobTypeID
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sql, con, 1

Your SQL is saying that the value currently in sVal is the NAME of a field in JobTypesT and that JobTypesID will match a value in the combo box cboJobTypeID. Since we don't know what is in sVal we can't tell whether it is valid. However, if sVal contains a VALUE that should be in some field in JobTypesT, that is a case where something will not be found in the collection.

That SQL is incredibly hard to read. However, if you have two tables with the same name for a given field, that isn't necessarily bad... particularly if there is a relationship between the tables based on the fields with the same names. I.e. perfectly legal and not an unreasonable strategy. However, if ALL of the fields are named the same, I would guess you have some redundancy in your table design. Forgive me if I don't wade through that in greater detail. However, I will say this. IF I read it correctly in spot-checking, your updates are putting values from the same variables in both tables, which would suggest that the tables are superficially the same. There is an inherent logic flaw in doing this with a relational database.

I only noticed something like one field different between the two tables. (I admit I could have missed more.) If they are that close and have the same data (not just same fields but same DATA in the fields), then you need to learn about database normalization and JOIN clauses. Normalization would allow you to drastically reduce the redundancy. I won't harp on that, but it is something to be considered.

One last comment as an addendum: In the code in the second window, you build two sql strings but only execute one of them.

 

[theDBguy]

Hi. Just a guess... SQL is a reserved word. Maybe try giving your variable a different name. The most usual one is strSQL.

 

[Chief]

Hi. Just a guess... SQL is a reserved word. Maybe try giving your variable a different name. The most usual one is strSQL.

G'Day,
Thank you for the reply, I tried changing that.
Still same error.

 

[arnelgp]

"sql" is not a reserved word.

if GetDays() function is in "same" form, then your code will work with Me.cboJobTypeID.
if in a Module, no.

 

[Chief]

"sql" is not a reserved word.

if GetDays() function is in "same" form, then your code will work with Me.cboJobTypeID.
if in a Module, no.

Thank you,
Yes same form.

 

[Chief]

In this context, what is the value of the call argument sVal and do you have a field of that name?

In this sequence

    sql = "SELECT " & sVal & " FROM JobTypesT WHERE JobTypesID = " & Me.CboJobTypeID
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sql, con, 1

Your SQL is saying that the value currently in sVal is the NAME of a field in JobTypesT and that JobTypesID will match a value in the combo box cboJobTypeID. Since we don't know what is in sVal we can't tell whether it is valid. However, if sVal contains a VALUE that should be in some field in JobTypesT, that is a case where something will not be found in the collection.

That SQL is incredibly hard to read. However, if you have two tables with the same name for a given field, that isn't necessarily bad... particularly if there is a relationship between the tables based on the fields with the same names. I.e. perfectly legal and not an unreasonable strategy. However, if ALL of the fields are named the same, I would guess you have some redundancy in your table design. Forgive me if I don't wade through that in greater detail. However, I will say this. IF I read it correctly in spot-checking, your updates are putting values from the same variables in both tables, which would suggest that the tables are superficially the same. There is an inherent logic flaw in doing this with a relational database.

I only noticed something like one field different between the two tables. (I admit I could have missed more.) If they are that close and have the same data (not just same fields but same DATA in the fields), then you need to learn about database normalization and JOIN clauses. Normalization would allow you to drastically reduce the redundancy. I won't harp on that, but it is something to be considered.

One last comment as an addendum: In the code in the second window, you build two sql strings but only execute one of them.

G'Day,

The highlighted section returns sql = "SELECT Lead_Date FROM JobTypesT WHERE JobTypesID = 2"
Which is correct.
The next section dValue = rs(0) I belive this should be a number like 3

In the JobInfoT the fields such as Lead_Date or IFA_Due these are date fields.
In the JobTypesT the fields are a number. These indicate how many days for the field.

And, Yes I think I don't need both sql statements... Need to look at that. I have commented out one of them and still getting error

Does that Help?

thanks

EDIT -
Here is the table

 

[arnelgp]

you never Declare your variable dValue.

 

[arnelgp]

try to break your sql into smaller pieces, maybe you got misspelling in your fieldname or fieldname is not in the table:

Dim iLead_Date As Integer, iIFA_Due As Integer

iLead_Date = GetDays("Lead_Date")
iIFA_Due = GetDays("SET IFA_Due")
' rest of your variable here

but wait...

look at the second line of your 2nd sql, you have a SET there:

sql = "UPDATE JobInfoT SET Lead_Date = #" & AddOrderDays(GetDays("Lead_Date")) & _
"#,SET IFA_Due = #" & AddOrderDays(GetDays("SET IFA_Due"))

 

[Chief]

try to break your sql into smaller pieces, maybe you got misspelling in your fieldname or fieldname is not in the table:

Dim iLead_Date As Integer, iIFA_Due As Integer

iLead_Date = GetDays("Lead_Date")
iIFA_Due = GetDays("SET IFA_Due")
' rest of your variable here

but wait...

look at the second line of your 2nd sql, you have a SET there:

sql = "UPDATE JobInfoT SET Lead_Date = #" & AddOrderDays(GetDays("Lead_Date")) & _
"#,SET IFA_Due = #" & AddOrderDays(GetDays("SET IFA_Due"))

"look at the second line of your 2nd sql, you have a SET there:"
Hmm Yes I saw that too and have removed it.

Ok, Ill try and start again.
I have had this work in another db, but I have changed things around in this one.

Here is the form with the due dates that need populating

Here is the "Update Button code" (This is temporary, as I will have call functions to this)

Private Sub BtnUpdate_Click()
    Dim db As Database
    Dim sql As String, sVal1 As String, sVal2 As String
    Dim con As Object
    Dim rs As Object
    Dim dDate As Date
    Dim iLoop As Integer, iDays As Integer ', iAcct As Integer

    Set con = Application.CurrentProject.Connection
    Set db = CurrentDb

'Not using this in this DB
        '    CboCustomerID.SetFocus
        '    If CboCustomerID.Text = "" Then
        '        MsgBox "Please select a customer from the list", vbInformation, "Select Customer"
        '        Exit Sub
        '    End If

    CboJobTypeID.SetFocus

    If CboJobTypeID.Text = "" And Nz(Me.Lead_Date, "") = "" Then
        Exit Sub
    End If

    If CboJobTypeID.Text = "" Then
        MsgBox "Please select a Job Type from the list", vbInformation, "Select Job Type"
        Exit Sub
    End If
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    If Lead_Date.Value & "" = "" Then
        Lead_Date.Value = Now()
    End If
    
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Me.Refresh

'Not using this in this DB
        '    sql = "SELECT systemdays FROM customer INNER JOIN PaymentT ON PaymentT.PaymentID = CustomerT.PaymentID WHERE CustomerID = " & Me.CboCustomerID
        '    Set rs = CreateObject("ADODB.Recordset")
        '    rs.Open sql, con, 1
        '
        '    If rs.EOF Or rs.BOF Then
        '        iDays = 4
        '    Else
        '        iDays = rs(0)
        '        rs.Close
        '        Set rs = Nothing
        '    End If

'Note to self - Which of these should I be Using?
    
'        sql = "UPDATE JobTypesT SET IFA_Due = #" & AddDays(GetDays("IFA_Due")) & "#,SampleSubm_Due = #" & AddDays(GetDays("SampleSubm_Due")) & _
'        "#,IFC_Due = #" & AddDays(GetDays("IFC_Due")) & "#,SetOutDue = #" & AddDays(GetDays("SetOutDue")) & _
'        "#,CarcassCut_Due = #" & AddDays(GetDays("CarcassCut_Due")) & "#,CarcassEdge_Due = #" & AddDays(GetDays("CarcassEdge_Due")) & _
'        "#,PFBCut_Due = #" & AddDays(GetDays("PFBCut_Due")) & "#,PFBEdge_Due = #" & AddDays(GetDays("PFBEdge_Due")) & _
'        "#,WhiteSatinCut_Due = #" & AddDays(GetDays("WhiteSatinCut_Due")) & "#,TwoPakPartsOut_Due = #" & AddDays(GetDays("TwoPakPartsOut_Due")) & _
'        "#,PickHW_Due = #" & AddDays(GetDays("PickHW_Due")) & "#,HingeDrill_Due = #" & AddDays(GetDays("HingeDrill_Due")) & _
'        "#,MachineShop_Due = #" & AddDays(GetDays("MachineShop_Due")) & "#,DrawerAss_Due = #" & AddDays(GetDays("DrawerAss_Due")) & _
'        "#,AssemblyDue = #" & AddDays(GetDays("AssemblyDue")) & "#,TwoPakUnderC_Due = #" & AddDays(GetDays("TwoPakUnderC_Due")) & _
'        "#,TwoPakPaint_Due = #" & AddDays(GetDays("TwoPakPaint_Due")) & "#,WrapQC_Due = #" & AddDays(GetDays("WrapQC_Due")) & _
'        "#,Delivery_Due = #" & AddDays(GetDays("Delivery_Due")) & "# WHERE JobID = " & Me.JobID
        
'I think this one
        sql = "UPDATE JobInfoT SET Lead_Date = #" & AddOrderDays(GetDays("Lead_Date")) & _
        "#,IFA_Due = #" & AddOrderDays(GetDays("IFA_Due")) & "#,SampleSubm_Due = #" & AddOrderDays(GetDays("SampleSubm_Due")) & _
        "#,IFC_Due = #" & AddOrderDays(GetDays("IFC_Due")) & "#,SetOutDue = #" & AddOrderDays(GetDays("SetOutDue")) & _
        "#,CarcassCut_Due = #" & AddOrderDays(GetDays("CarcassCut_Due")) & "#,CarcassEdge_Due = #" & AddOrderDays(GetDays("CarcassEdge_Due")) & _
        "#,PFBCut_Due = #" & AddOrderDays(GetDays("PFBCut_Due")) & "#,PFBEdge_Due = #" & AddOrderDays(GetDays("PFBEdge_Due")) & _
        "#,WhiteSatinCut_Due = #" & AddOrderDays(GetDays("WhiteSatinCut_Due")) & "#,TwoPakPartsOut_Due = #" & AddOrderDays(GetDays("TwoPakPartsOut_Due")) & _
        "#,PickHW_Due = #" & AddOrderDays(GetDays("PickHW_Due")) & "#,HingeDrill_Due = #" & AddOrderDays(GetDays("HingeDrill_Due")) & _
        "#,MachineShop_Due = #" & AddOrderDays(GetDays("MachineShop_Due")) & "#,DrawerAss_Due = #" & AddOrderDays(GetDays("DrawerAss_Due")) & _
        "#,AssemblyDue = #" & AddOrderDays(GetDays("AssemblyDue")) & "#,TwoPakUnderC_Due = #" & AddOrderDays(GetDays("TwoPakUnderC_Due")) & _
        "#,TwoPakPaint_Due = #" & AddOrderDays(GetDays("TwoPakPaint_Due")) & "#,WrapQC_Due = #" & AddOrderDays(GetDays("WrapQC_Due")) & _
        "#,Delivery_Due = #" & AddOrderDays(GetDays("Delivery_Due")) & "# WHERE JobID = " & Me.JobID

    db.Execute sql
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Me.Refresh
    
End Sub

MORE TO COME..

 

[Chief]

'------------------------------------------------------------

'Job Type Lead Times *****(Working on)******

'

'------------------------------------------------------------

Private Sub CboJobTypeID_Click()

    Dim db As Database

    Dim sql As String

    Dim con As Object

    Dim rs As Object



    Set con = Application.CurrentProject.Connection

    Set db = CurrentDb

  

    Me.TxtDescription.Value = Me.CboJobTypeID.Column(2)

  

    sql = "SELECT JobDescription, LeadTime, FROM JobTypesT WHERE JobTypeID = " & Me.CboJobTypeID

    Set rs = CreateObject("ADODB.Recordset")

    rs.Open sql, con, 1



'    If Not rs.EOF And Not rs.BOF Then

'        TxtDescription = rs(0)

'        i = 1

'        If chkBxAssembled = True Then

            Lead_Date.Value = AddOrderDaysReq(rs(1) + i)

'        Else

'            Lead_Date.Value = AddOrderDaysReq(rs(2) + i)

'        End If

'        If rs(1) = True Then

'        Me.ReworkReason.Enabled = True

'        Else

'        Me.ReworkReason.Enabled = False

'        End If

'    End If



    rs.Close

    Set rs = Nothing



    Call BtnUpdate_Click



End Sub

[/CODE]

And here is some other related code "Get Days"

Function GetDays(sVal)

    Dim db As Database
    Dim sql As String, sVal1 As String, sVal2 As String
    Dim con As Object
    Dim rs As Object
    Dim dValue As Double
   
    Set con = Application.CurrentProject.Connection
    Set db = CurrentDb
   
    sql = "SELECT " & sVal & " FROM JobTypesT WHERE JobTypesID = " & Me.CboJobTypeID
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sql, con, 1
   
    dValue = rs(0)
    rs.Close
    Set rs = Nothing
 
    GetDays = dValue
End Function

"Add Order Days"

Function AddOrderDays(iVal As Integer)

    Dim dtdate As Date

    dtdate = JobConfirmed.Value
    i = 0
   
    While i < iVal
       
        If CheckHoliday(dtdate) Then
            dtdate = DateAdd("d", 1, dtdate)
        Else
            Select Case (Weekday(dtdate))
                Case 1: dtdate = DateAdd("d", 1, dtdate)
                Case 2: dtdate = DateAdd("d", 3, dtdate)
                Case 3: dtdate = DateAdd("d", 1, dtdate)
                Case 4: dtdate = DateAdd("d", 1, dtdate)
                Case 5: dtdate = DateAdd("d", 1, dtdate)
                Case 6: dtdate = DateAdd("d", 1, dtdate)
                Case 7: dtdate = DateAdd("d", 2, dtdate)
            End Select
            i = i + 1
        End If
    Wend
   
    While i <= iVal
       
        If CheckHoliday(dtdate) Then
            dtdate = DateAdd("d", 1, dtdate)
            Select Case (Weekday(dtdate))
                Case 1: dtdate = DateAdd("d", 1, dtdate)
                Case 2: dtdate = DateAdd("d", 3, dtdate)
                Case 7: dtdate = DateAdd("d", 2, dtdate)
            End Select
        Else
            i = i + 1
        End If
    Wend
   
    AddOrderDays = Month(dtdate) & "/" & Day(dtdate) & "/" & Year(dtdate)
End Function

"Add Days"

Function AddDays(iVal As Integer)
    Dim dtdate As Date

    If CustomerPreferredDate = "" Or IsNull(CustomerPreferredDate) Then
        dtdate = Lead_Date.Value
    Else
        dtdate = CustomerPreferredDate.Value
    End If
    i = 0
   
    dtdate = DateAdd("d", -1, dtdate)
   
    While i < iVal
           
        If CheckHoliday(dtdate) Then
            dtdate = DateAdd("d", -1, dtdate)
        Else
            Select Case (Weekday(dtdate))
                Case 1: dtdate = DateAdd("d", -2, dtdate)
                Case 2: dtdate = DateAdd("d", -3, dtdate)
                        i = i + 1
                Case 3: dtdate = DateAdd("d", -1, dtdate)
                        i = i + 1
                Case 4: dtdate = DateAdd("d", -1, dtdate)
                        i = i + 1
                Case 5: dtdate = DateAdd("d", -1, dtdate)
                        i = i + 1
                Case 6: dtdate = DateAdd("d", -1, dtdate)
                        i = i + 1
                Case 7: dtdate = DateAdd("d", -1, dtdate)
                        i = i + 1
            End Select
        End If
    Wend
   
    While i <= iVal
           
        If CheckHoliday(dtdate) Then
            dtdate = DateAdd("d", -1, dtdate)
            Select Case (Weekday(dtdate))
                Case 1: dtdate = DateAdd("d", -2, dtdate)
                Case 2: dtdate = DateAdd("d", -3, dtdate)
            End Select
        Else
            i = i + 1
        End If
    Wend
   
    AddDays = Day(dtdate) & "/" & Month(dtdate) & "/" & Year(dtdate)
   
End Function

This code however is all I could find showing the Dim dValue (however this table has nothing to do with these dates)

'------------------------------------------------------------
'Board and Edging Values *****(Working on)******
'
'------------------------------------------------------------
Function Get_Value(sVal)

    Dim db As Database
    Dim sql As String, sVal1 As String, sVal2 As String
    Dim con As Object
    Dim rs As Object
    Dim dDate As Date
    Dim dValue As Double

    Set con = Application.CurrentProject.Connection
    Set db = CurrentDb

    sql = "SELECT " & sVal & " FROM BrdEdgeValueT"
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open sql, con, 1

    dValue = rs(0)
    rs.Close
    Set rs = Nothing

    Get_Value = dValue
End Function

I know its alot, sorry, but im stuck now..

 

[arnelgp]

it is jus somewhere in your sql, you need to break it down.
otherwise you will pass the whole sql (with error).
like i said, put all into a variable first and determine
where the error is coming from:

Dim iLead_Date As Integer, iIFA_Due As Integer

iLead_Date = GetDays("Lead_Date")
iIFA_Due = GetDays("SET IFA_Due")
' rest of your variable here

then on your query just plug your variable.


sql = "UPDATE JobInfoT SET Lead_Date = #" & AddOrderDays(iLead_Date ) & _
"#,IFA_Due = #" & AddOrderDays(iIFA_Due) & ...

 

[theDBguy]

sql = "SELECT JobDescription, LeadTime, FROM JobTypesT WHERE JobTypeID = " & Me.CboJobTypeID

In CboJobTypeID_Click, you have the above SQL. There's an extra comma in it after LeadTime and before FROM.

 

[The_Doc_Man]

Good catch, DBG!

 

[Chief]

it is jus somewhere in your sql, you need to break it down.
otherwise you will pass the whole sql (with error).
like i said, put all into a variable first and determine
where the error is coming from:

Dim iLead_Date As Integer, iIFA_Due As Integer

iLead_Date = GetDays("Lead_Date")
iIFA_Due = GetDays("SET IFA_Due")
' rest of your variable here

then on your query just plug your variable.


sql = "UPDATE JobInfoT SET Lead_Date = #" & AddOrderDays(iLead_Date ) & _
"#,IFA_Due = #" & AddOrderDays(iIFA_Due) & ...

Ok, I'm on it.
Ill be back, Thanks Men

 

[Chief]

Morning Men,
I have made some progress, managed to get the lead Date populating now.
However still getting a "Run-time error '3061': Too fe parameters, Expected 1.

Not sure what this means.
When I debug print it looks like I am getting the dates. (Shown below)
UPDATE JobInfoT SET IFA_Due = #5/10/2021#,SampleSubm_Due = #5/10/2021#,IFC_Due = #5/10/2021#,SetOutDue = #11/10/2021#,CarcassCut_Due = #21/10/2021#,CarcassEdge_Due = #21/10/2021#,PFBCut_Due = #21/10/2021#,PFBEdge_Due = #21/10/2021#,WhiteSatinCut_Due = #21/10/2021#,TwoPakPartsOut_Due = #8/10/2021#,PickHW_Due = #22/10/2021#,HingeDrill_Due = #20/10/2021#,MachineShop_Due = #28/10/2021#,DrawerAss_Due = #26/10/2021#,AssemblyDue = #28/10/2021#,TwoPakUnderC_Due = #21/10/2021#,TwoPakPaint_Due = #29/10/2021#,WrapQC_Due = #29/10/2021#,Delivery_Due = #3/11/2021# WHERE JobID = 321

Any suggestions where I look from here please?

 

[The_Doc_Man]

While I don't see anything obvious, that message sometimes occurs when you use a variable name that happens to match up to a single-argument function. I.e. accidental name match. Does the debugger call out the statement that executes the SQL or does it call out something else?

 

[Chief]

While I don't see anything obvious, that message sometimes occurs when you use a variable name that happens to match up to a single-argument function. I.e. accidental name match. Does the debugger call out the statement that executes the SQL or does it call out something else?

 

[theDBguy]

See if the information here helps any.

Microsoft Access tips: International Dates in Access

How to ensure dates are interpreted correctly in a Microsoft Access database, even when the user's regional settings are different from the US format.

allenbrowne.com

 

[The_Doc_Man]

OK, "AddDays" is a function and one of its most common arguments, GetDays, is ALSO a function. You supplied them both in an earlier post, so I took a quick scan but didn't see anything obvious. Doesn't prove anything because some days I can't see doodlum squat. Side effect of having a mild touch of macular degeneration.

More significantly, you are executing the functions in SQL context so detailed error reporting is going to be limited. All that CAN be called out by Access is the db.Execute because everything that happens in SQL occurs in the separate memory & context of the ACE engine. So in essence, the data flow is that Access passes the SQL to ACE, which barfs - but doesn't have visibility of the individual lines of code, so all it can do is return an error number. The only thing that Access saw was that passing this SQL caused this error - which it reports.

If this function is executed in the Access GUI, you might get a more specifically directed error. As a contrived test, run some code to somehow call AddDays using GetDays in some event code (strictly for debugging purposes). I.e. fake out a call to the AddDays and GetDays functions so that you can see what comes back from the call in Access GUI context. Don't test it using SQL context. IF there is an error in either of these items when called from Access's GUI/VB context, the error trap might be more specific and call out the line that actually is failing somewhere in the subroutine.

If that doesn't help, then I'm definitely clueless.