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:
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:
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
' Empty the memory
Set db = Nothing
Set qdf = Nothing
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.
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
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.
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).
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.