Run-time error '3075' Syntax error (comma) in query expression

[jeryoyo24]

Hi, I have a form and I keep getting Run-time error '3075' Syntax error (comma) in query expression and it keep pointing me to "DoCmd.runsql strAddStudent". I still cannot figure out where is the error. Below is my partial code. Any expert can guide me thru this? I am new to MS Access and coding.

Private Sub btn_save_Click()

Dim sMissingValues As String
Dim acked As Integer


Dim db As Database
Set db = CurrentDb


Dim strAddStudent As String


sMissingValues = ""


If Len(Me.txtStudentName & vbNullString) = 0 Then sMissingValues = sMissingValues + vbCrLf + "- Student Name is required"
If Len(Me.txtStudentID & vbNullString) = 0 Then sMissingValues = sMissingValues + vbCrLf + "- Student ID is required"
If Len(Me.txtStudentClass & vbNullString) = 0 Then sMissingValues = sMissingValues + vbCrLf + "- Student Class is required"


If sMissingValues <> "" Then
    MsgBox prompt:="Please fill in the missing fields:" & vbCrLf & sMissingValues, buttons:=vbCritical, title:="Missing Information"
    
Else
acked = MsgBox("Confirm to save student record?", vbYesNo)


If acked = vbYes Then


strAddStudent = "insert into tbl_student_details (createdBy, studentName, studentID, studentClass, dateAdded) VALUES ((StrConv([TempVars]![tvarStaffName].[value],1), '" & Me.[txtStudentName] & "', '" & Me.[txtstudentID] & "', '" & Me.[txtstudentClass] & "', NOW() )"


    DoCmd.runsql strAddStudent
    MsgBox prompt:="Successfully Saved", buttons:=vbInformation, title:="Great"


    
    Else
    MsgBox prompt:="Student Record not Save", buttons:=vbInformation, title:="Oh No"
    
End If


End If


End Sub

[jeryoyo24]

I changed (StrConv([TempVars]![tvarStaffName].[value],1) to ([TempVars]![tvarStaffName].[value]) and is working. But how do I make it such that it will save the Staff Name in all upper case?

[Micron]

Your parentheses are incorrect. You're putting the StrConv argument inside the TempVars parentheses.

(StrConv([TempVars]![tvarStaffName].[value]),1)

[jeryoyo24]

Your parentheses are incorrect. You're putting the StrConv argument inside the TempVars parentheses.

(StrConv([TempVars]![tvarStaffName].[value]),1)

Thanks, but I still have the same error..

At the moment, I changed my table, under format, by adding ">" and caption as StaffName.. When I saved it will be uppercase. Not sure if this will cause any issue in the long run.

[Micron]

VBA SQL 101 trouble shooting: output to immediate window, copy, paste into a new query in sql design view and run it if it is a SELECT query or switch to datasheet view if it is an action query. If it balks, 9 times out of 10 it highlights the offending syntax, or at least comes close.

strSql = "INSERT INTO..."
debug.print strSql
Stop

When you look at the output, see what your variables and references are coming out as. A value from TempVars or form control, etc. might not be what you think it is.
Don't forget to remove the Stop line (or just put a break point on your line that runs the sql instead).
Also, UCase function will force all characters in a string to upper case if that's what you want to do.

[jeryoyo24]

Thanks. I realised I accidentally removed a ) and additional comma.

[jeryoyo24]

Anyway, my form has a combo box (Gender) and whenever i add a new record, it will always have an empty row. This also happened to my another table which also have combo box. I am unable to upload image as it keep logging me out. Below is an example for illustration. Anyone can advise?

ID | createdBy | studentName | Gender | studentID | studentClass | dateCreated
1 | empty| empty | Please Select | empty| empty| empty
2 | Admin | Mike | Male | S10001A | W16 | 20/12/2021
3 | Admin | Man | Male | S10005A | E16 | 20/12/2021

Edit: I removed all the combo box default values "Please Select" and is fine. Just that the ID number will jump.
E.g. Table A
1
2

Table B
2
3

[Micron]

Sorry, I can't make sense of that. Can you copy, compact and zip a copy of your db and post it here? Instructions can be found from the link in the banner at the top of this page should you need them. If you do, include instructions on how to replicate the problem, what that problem is and what the result should be.

[jeryoyo24]

Hi Micron, I am unable to upload any file (zip, images). I keep getting logged out. Tried on other browser also the same

it keep prompting me error.

The following errors occurred:
You do not have permission to perform this action. Please refresh the page and login before trying again.

[Micron]

If there is a post limit before you can upload an attachment I've forgotten about it. By the time that is sorted out, I imagine you will have reached that limit, which I imagine to be ten. Perhaps a moderator can advise in the meantime.

[jeryoyo24]

Oh ok.. I have not reach 10 post count.

[Micron]

I think there is a 10 post minimum - not sure. By the time we finish discussing that, you might reach it! You're at 7 now.

[jeryoyo24]

I have another form with textbox. I will use this validation checks as an example.

If Len(Me.txtStudentName & vbNullString) = 0 Then sMissingValues = sMissingValues + vbCrLf + "- Student Name is required"
If Len(Me.txtStudentID & vbNullString) = 0 Then sMissingValues = sMissingValues + vbCrLf + "- Student ID is required"
If Len(Me.txtStudentClass & vbNullString) = 0 Then sMissingValues = sMissingValues + vbCrLf + "- Student Class is required"

If all the textbox box are empty, I will require user to put 'NA' in the reasons textbox (txtReasons).

How do I edit the validation checks for this?

[Micron]

Lots of different things you could do to handle this. The test could be
If Len(Me.txtStudentName & vbNullString) + Len(Me.txtStudentID & vbNullString + Len(Me.txtStudentClass & vbNullString) = 0 Then

If just one value isn't missing, your requirement has not been met. Whatever code you use, it should be in the form BeforeUpdate event so that you can prevent the record from being saved by using Cancel = True

[jeryoyo24]

Ok thanks. I managed to break down my validation checks and is working.