Page 1 of 3 123 LastLast
Results 1 to 15 of 38
  1. #1
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820

    Unanswered: what the F**K is Wrong this time

    why is this not working

    Code:
    Private Sub S_DATE_AfterUpdate()
        UpdateDates S_DATE.Value
    End Sub
    
    Private Sub E_DATE_AfterUpdate()
        UpdateDates E_Date.Value
    End Sub
    
    Sub UpdateDates(val As Date)
        Dim db As Database, rs As Recordset
        Set db = CurrentDb
        Set rs = db.OpenRecordset("dates", dbOpenTable) '<-- type Mismatch
        rs.Find ("dates = #" & val & "#")
        If (rs.EOF = True) Then
            With rs
                .AddNew
                !dates = val
                .update
            End With
        End If
    End Sub
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Re: what the F**K is Wrong this time

    Why not use a select statment when you define the recordset ie:

    Sub UpdateDates(val As Date)
    Dim db As Database, rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("dates", "Select * from Date Table")
    rs.Find ("dates = #" & val & "#")
    If (rs.EOF = True) Then
    With rs
    .AddNew
    !dates = val
    .update
    End With
    End If
    End Sub



    Originally posted by m.timoney
    why is this not working

    Code:
    Private Sub S_DATE_AfterUpdate()
        UpdateDates S_DATE.Value
    End Sub
    
    Private Sub E_DATE_AfterUpdate()
        UpdateDates E_Date.Value
    End Sub
    
    Sub UpdateDates(val As Date)
        Dim db As Database, rs As Recordset
        Set db = CurrentDb
        Set rs = db.OpenRecordset("dates", dbOpenTable) '<-- type Mismatch
        rs.Find ("dates = #" & val & "#")
        If (rs.EOF = True) Then
            With rs
                .AddNew
                !dates = val
                .update
            End With
        End If
    End Sub

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    well that stopped the 'type mismatch' error unforchanatly that line now creates a 'data type conversion error'
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  4. #4
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by m.timoney
    well that stopped the 'type mismatch' error unforchanatly that line now creates a 'data type conversion error'
    Thats probably because the field you are selecting frmo out of the table is not in the date formate. try to convert it or convert your variables.
    Jim

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    how would that stop me opening the record set? because it wont do it, in fact i don't seen to be able to open anything with openrecordset command

    anyway the table is called dates and the only field is called dates and is in Date format
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Post your updated code for the recordset
    Jim

  7. #7
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    maybe a bit of background may help
    the form is show the results of
    Code:
    SELECT TotalPack.ICEDATE, TotalPack.AccountNo, TotalPack.NAME, TotalPack.SHIP_TO, 
    TotalPack.Product, TotalPack.PackPart, TotalPack.Dscrp, TotalPack.Weight, 
    TotalPack.Unit, TotalPack.WHSE
    FROM TotalPack
    WHERE (((TotalPack.ICEDATE) Between [forms]![perCust]![S_Date] And 
    [forms]![perCust]![E_Date]) AND ((TotalPack.AccountNo) Like 
    [forms]![perCust]![cust] & "*"))
    ORDER BY TotalPack.ICEDATE, 
    TotalPack.NAME;
    now instead of having the user continely retypening the dates i want them to be entered in to a table, which feeds the combo box they are using, this will allow them to rapidly access dates with out me having to enter every date between 01/01/2000 to date when database is no longer used could be (next week or the end of time)

    now the reason i say that i don't seem to be able to open anything with the openrecordset method is that a collaegue when asked if he could see the problem pointed out that i's asked had the same problem about a month ago, totaly different data base table names the works, that time i found a work round method but that method wont work in this case.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  8. #8
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by JDionne
    Post your updated code for the recordset
    Jim
    these are the versions i've tryed

    Sub UpdateDates(val As Date)
    Dim db As Database, rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Dates")
    rs.Find ("dates = #" & val & "#")
    If (rs.EOF = True) Then
    With rs
    .AddNew
    !dates = val
    .update
    End With
    End If
    End Sub

    Sub UpdateDates(val As Date)
    Dim db As Database, rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select * from Dates;")
    rs.Find ("dates = #" & val & "#")
    If (rs.EOF = True) Then
    With rs
    .AddNew
    !dates = val
    .update
    End With
    End If
    End Sub

    Sub UpdateDates(val As Date)
    Dim db As Database, rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("dates","Select * from Dates;")
    rs.Find ("dates = #" & val & "#")
    If (rs.EOF = True) Then
    With rs
    .AddNew
    !dates = val
    .update
    End With
    End If
    End Sub
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  9. #9
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    ok so you are trying to get a recordset that has a specific time frame after an end user selects the time range

    im not sure about this code:

    rs.Find ("dates = #" & val & "#")
    If (rs.EOF = True) Then
    With rs
    .AddNew
    !dates = val
    .update
    End With
    End If

    but i have an idea as to how to limit the recordset. Try:

    Sub UpdateDates(val As Date)
    Dim db As Database, rs As Recordset , start_time as string, end_time as string
    Set db = CurrentDb
    Set rs = db.OpenRecordset("dates","Select * from Dates" & _
    " where date between #" & start_time & "# and #" & end_time

    End Sub

    that will limit your recordset and then you can use the recordset as the soruce of the form.

    form.rowsorucetype = "table/query"
    form.rowsource = "select * from dates"

    does this help???
    Jim

  10. #10
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    lets try that again,

    what i want to do is open the recordset (Cant do),
    see if the combo box value excists in the recordset,
    if not put it in.

    the value in the combo box is then used in the above query to limit the records in the Per Customer Packaging query above
    Last edited by m.timoney; 12-18-02 at 12:09.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  11. #11
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    ohh......Forgive me for asking but if this new date needs to be added to
    the recordset, does that mean that it needs to be in the base table? as well as do you get the error when you check to see if the date is in the recordset or when you try to insert it.
    if its at the insert step you may have to insert it into the base table and then recreate the recordset
    if its at the checking part then we need to look at that.
    sorry for all the questions but i think we are getting some where
    Jim

    Originally posted by m.timoney
    lets try that again,

    what i want to do is open the recordset (Cant do),
    see if the combo box value excists in the recordset,
    if not put it in.

    the value in the combo box is then used in the above query to limit the records in the Per Customer Packaging query above

  12. #12
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i get the error on the openrecordset command the program don't even get to the checking or inserting and like i said this isn't the first time i've found my self unable to open a recordset it's eveytime.

    Originally posted by JDionne
    as well as do you get the error when you check to see if the date is in the recordset or when you try to insert it.
    Jim

    yes thats the entire point, now i may be wrong about this but i was working under the impression that if you opened the recordset in table mode ie dbOpenTable that any changes to the recordset are put straight in the table it's created from

    Originally posted by JDionne
    ohh......Forgive me for asking but if this new date needs to be added to
    the recordset, does that mean that it needs to be in the base table?
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  13. #13
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    maybe the recordset is completed correctly and its the rs.find function thats haveing the problem. maybe its not handeling that variable correctly.

    try to hard code a date in that find commcand and see what that does.
    Jim

  14. #14
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    no, because the code isn't getting to the rs.find, it can't get past the
    set rs = openrecordset...
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  15. #15
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    i'll prove this for you

    Sub test()
    Dim db As Database, rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("dates")
    End Sub

    this code causes the same error
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •