Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2009
    Posts
    37

    Unanswered: Save data based on tickbox

    Hi all,

    Hopefully this will be my last problem for finishing my database about Wild Dogs.
    I have a form (frmCollaring) and two tables (tblCollaring and tblSamples).
    During our work we collect various samples but at a collaring session we collect samples as well.

    I have a form wich fills in samples alright, but I also have a form to fill in data from a collaring session which saves the data in tblCollaring.
    This from (frmCollaring) has some tickboxes for a couple of sampletypes, like 'Blood Sample' and 'Tissue Sample'.

    What I'd like the form to do is to save various fields (Date, CollaringID, DogID, Sample type) from the form to the table tblSamples.

    I tried to write a function but it's giving me headaches.
    After some searching I came up some codes but I'm just not getting there. I think I'm mixing up what should be tbl and what frm and I keep getting errors.
    The closest I came (I think) is with this code which gives the following error msg:
    Code:
    Syntax error in query-expression ((frmCollaring.CollaringID = 1) AND
     (frmCollaring.DogID = M001) AND (frmCollaring.Date = 12-2-2009) AND
     (frmCollaring.[Sample Type] = Tissue;)).
    So it selects the right values from the form, now all it has to do is to save it to the table tblSamples.

    Here's the code so far:
    Code:
    On Error GoTo Err_Handler
    
    ' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    ' Get the database and stored query
    Set db = CurrentDb()
    'Set qdf = db.QueryDefs("AppendSamples")
    If Me!Selection = True Then
    strCriteria = "((frmCollaring.CollaringID = " & Me!CollaringID & ") " & _
    "AND (frmCollaring.DogID = " & Me!DogID & ") " & _
    "AND (frmCollaring.Date = " & Me!Date & ") " & _
    "AND (frmCollaring.[Sample Type] = " & Me![Sample Type] & ";))"
    ' Build the new SQL statement incorporating the string
    strSQL = "INSERT INTO tblSamples ( CollaringID, DogID, [Date], [Sample Type] ) " & _
    "SELECT frmCollaring.CollaringID, frmCollaring.DogID, frmCollaring.Date, frmCollaring.[Sample Type] " & _
    "FROM frmCollaring " & _
    "WHERE " & strCriteria
    DoCmd.RunSQL strSQL
    Exit Sub
    End If
    ' Empty the memory
    Set db = Nothing
    Set qdf = Nothing
    
    Exit_Handler:
    Exit Sub
    
    Err_Handler:
    MsgBox Err.Description
    Resume Exit_Handler
    
    End Sub
    Mind you, I've just adjusted the code from another example. So there might be rubbish in there which I don't need and could be deleted. I understand some of the code, but not all of it.

    Hope somebody can help me out!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Haven't looked at all of it, but the cause of your error is that date/time values must be surrounded by #, and text values by '. This type of thing:

    "AND (frmCollaring.Date = #" & Me!Date & "#) " & _

    Also, "Date" should not be used as a field name, as Access can get confused between it and the Date() function.
    Paul

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    change
    strCriteria = "((frmCollaring.CollaringID = " & Me!CollaringID & ") " & _
    "AND (frmCollaring.DogID = " & Me!DogID & ") " & _
    "AND (frmCollaring.Date = " & Me!Date & ") " & _
    "AND (frmCollaring.[Sample Type] = " & Me![Sample Type] & " ; ))"

    to

    strCriteria = "((frmCollaring.CollaringID = " & Me!CollaringID & ") " & _
    "AND (frmCollaring.DogID = " & Me!DogID & ") " & _
    "AND (frmCollaring.Date = #" & Me!Date & "#) " & _
    "AND (frmCollaring.[Sample Type] = '" & Me![Sample Type] & "' ; ))"

    as me!date = a date value you need to put the # so computer now we are dealing with dates

    as me![Sample Type] is string value you will need to " or ' it
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Feb 2009
    Posts
    37
    I've changed the 'Date' value on the form to 'CollaringDate' and adjusted the strCriteria to what the example of Stephan, but it still gives me the same error, 'Syntax error in'...so I guess I'm missing something.
    I've attached the db as a zip (only took an hour or so, crappy african connection).
    Attached Files Attached Files

  5. #5
    Join Date
    Feb 2009
    Posts
    37
    Found another type-o, changed it but didn't help.
    Code:
    strCriteria = "((frmCollaring.CollaringID = " & Me!CollaringID & ") " & _
    "AND (frmCollaring.DogID = " & Me!DogID & ") " & _
    "AND (frmCollaring.Date = #" & Me!Date & "#) " & _
    "AND (frmCollaring.[Sample Type] = '" & Me![Sample Type] & "' ; ))"
    So I got the ; out but still the same error message...

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    frmCollaring is a form, not a table. In the SQL, you're using it where you would have a table name. Build VALUES clause instead:

    INSERT INTO...
    VALUES(...)

    and concatenate your form references into the VALUES clause.
    Paul

  7. #7
    Join Date
    Feb 2009
    Posts
    37
    I renamed all my forms to frmName and my tables to tblName. Still had errors.
    In the end it was a datum error. My data to check if it worked contained the date 12-02-2008. In Europe that means February 12, and so were the properties of the field set up: dd/mm/yyyy. But the SQL-engine only works with American dates. Since the computer who runs the database was set up with american region there were no big problems there.

    Thanks for the help!

Posting Permissions

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