gigifarrow
asked on
How to use IIf statement using three criterias if true then append the records. Syntax error but no clue whats wrong?
I am trying to append records based on three criterias if they meet these criterias then that record is appended. I am getting a error. But it just says syntax error. what am I doing wrong experts?
This code is comparing what is in the table it is appending to.
tblFS3Question = table that has data that needs to append to another table
tblFS3Responses= table that data needs to append to.
If the FormID is 1 in the tblFS3Responses and the count of the questiontext =76 and the time is the same then dont append it. else if this is not a true statement then append the record.
IIf([tblFS3Responses]![Que
This code is comparing what is in the table it is appending to.
tblFS3Question = table that has data that needs to append to another table
tblFS3Responses= table that data needs to append to.
If the FormID is 1 in the tblFS3Responses and the count of the questiontext =76 and the time is the same then dont append it. else if this is not a true statement then append the record.
IIf([tblFS3Responses]
It looks like you're missing closing parentheses.
ASKER
Thank you for your help I tried that but I get message that says syntax error and then it highlights
"qryAppendQuestions"
"qryAppendQuestions"
ASKER
the number one is a selection in the drop down. FormID is datatype is autonumber.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok so now i changed it to a if statement and it is not stoping dupilcates from appending.
This is a continuous form that the append query is appending.
formId = selection of the form they need to use. which could be 1-8
so if the serial number and form Id is the same then they cant save that data.
If DCount("*", "tblFS3Responses", "[Serial]=" & Chr(34) & SID & Chr(34) & [FormID] = " & Me.FormID & ")>0 Then
Below is one record. I cant remove duplicates because all the data is the same except for the answers.
Serial formID question# Question Name
139 3 151 Latch, Bar Drivers Hatch Rework: Zte, Juan
139 3 151 If Deficiencies Please Explain: Zte, Juan
139 3 152 TOW Missile Launcher & Zte, Juan
This is a continuous form that the append query is appending.
formId = selection of the form they need to use. which could be 1-8
so if the serial number and form Id is the same then they cant save that data.
If DCount("*", "tblFS3Responses", "[Serial]=" & Chr(34) & SID & Chr(34) & [FormID] = " & Me.FormID & ")>0 Then
Below is one record. I cant remove duplicates because all the data is the same except for the answers.
Serial formID question# Question Name
139 3 151 Latch, Bar Drivers Hatch Rework: Zte, Juan
139 3 151 If Deficiencies Please Explain: Zte, Juan
139 3 152 TOW Missile Launcher & Zte, Juan
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok I fixed that but it still keeps saying data mismatch.
If DCount("*", "tblFS3Responses", "[Serial]=" & Chr(34) & SID & Chr(34) And [FormID] = " Me.FormName & [QuestionID] = Me.frmFS3Questionaire.Form
If DCount("*", "tblFS3Responses", "[Serial]=" & Chr(34) & SID & Chr(34) And [FormID] = " Me.FormName & [QuestionID] = Me.frmFS3Questionaire.Form
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for the great information I am not getting a errror message but is not giving out a message when its duplicate.When I put a "And" in front of FormID I get a data missmatch error.
serial = Text
FormID = number (In the form the name of the field is FormName )
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.[Serial]
stLinkCriteria = "[Serial]=" & "'" & SID & "'"
If DCount("*", "tblFS3Responses", "[Serial] = '" & SID & "' &[FormID] = '" & Me.FormName & "'") > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Vehicle Serial Number " _
& SID & " WARNING, Duplicate Record!" _
& vbCr & vbCr & "You Can Not Save This Information.", _
vbInformation, "Duplicate Information"
'Go to record of original Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
'Go to record of original Serial Number
Set rsc = Nothing
Exit Sub
End If
serial = Text
FormID = number (In the form the name of the field is FormName )
Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.[Serial]
stLinkCriteria = "[Serial]=" & "'" & SID & "'"
If DCount("*", "tblFS3Responses", "[Serial] = '" & SID & "' &[FormID] = '" & Me.FormName & "'") > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Vehicle Serial Number " _
& SID & " WARNING, Duplicate Record!" _
& vbCr & vbCr & "You Can Not Save This Information.", _
vbInformation, "Duplicate Information"
'Go to record of original Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
'Go to record of original Serial Number
Set rsc = Nothing
Exit Sub
End If
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Are you sure FormID = "1" not 1
Open in new window
Regards