If Then Statement Referencing Table Columns

[Lrn2Code]

Hello,

I searched the site and couldn't seem to find a reference to the issue I'm dealing with. Am having a tough time getting an If Then statement to run properly. Am trying to find if an exact value is in a table and if it is not then the table needs to be updated to that new value. My code is below and it's running whether the item is in the table or not so I know something isn't right.

If strName <> ("tblschoolclasses.coursename") Then

strSQL = "Update tblschoolclasses set coursename = '" & strName & "' where courseid = " & lngID & " and POSID = '" & strPSID & "'"
Debug.Print (strSQL)
Set qdf = dbs.CreateQueryDef("", strSQL)
qdf.Execute

X = 1 (This is to calculate that the item has been updated)

End If

Included above this If Then statement is the following where the current values from the form are set -

strName = Me.txtName
lngID = Me.txtCourseID
strPSID = Me.txtPOS
strcoursecat = Me.cmbcoursecat

strSQL = "select * from tblschoolclasses where coursename = '" & strName & "' and POSID = '" & strPSID & "'"
​

In the ("tblschoolclasses.coursename") I'm getting an error 424 - Object Required. What's that all about?

Thanks in advance for any guidance you can provide.

 

[DCrake]

You don't need the brackets and quotes around the reference. Access can't evaluate it.

use

If str <> tblschoolclasses.coursename Then

BTW where is tblschoolclasses.coursename being picked up from?

 

[Lrn2Code]

From a table where all the school class information is kept.

Thank you so much!

 

[Rabbie]

I would use something like this to find out if you need to update the table

[COLOR=black]if dcount("coursename","[FONT=Arial Narrow][SIZE=3]tblschoolclasses", "coursename = strname") = 0  then 'No records found[/SIZE][/FONT][/COLOR]
[FONT=Arial Narrow][SIZE=3]  include code to add coursename to table[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=3]End if[/SIZE][/FONT]

PS looks like David beat me to it.

 

[Lrn2Code]

Thank you very much!

 

[Lrn2Code]

You don't need the brackets and quotes around the reference. Access can't evaluate it.

use

If str <> tblschoolclasses.coursename Then

BTW where is tblschoolclasses.coursename being picked up from?

For some reason I'm still getting the error 424 - Object required. Do you know what I may be doing wrong?

 

[Lrn2Code]

I would use something like this to find out if you need to update the table

[COLOR=black]if dcount("coursename","[FONT=Arial Narrow][SIZE=3]tblschoolclasses", "coursename = strname") = 0  then 'No records found[/SIZE][/FONT][/COLOR]
[FONT=Arial Narrow][SIZE=3]  include code to add coursename to table[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=3]End if[/SIZE][/FONT]

PS looks like David beat me to it.

I tried this and am getting an error 2001 - you canceled the previous operation. Why would it do that?

 

[gemma-the-husky]

possibly because the expression appears to be slightly incorrect

try

if dcount("*","tblschoolclasses", "coursename = " & chr(34) & strname & chr(34)) = 0  then 'No records found
  include code to add coursename to table
End if

assuming the field coursename is a field in the table tblschoolclasses, of type text

 

[Rabbie]

I tried this and am getting an error 2001 - you canceled the previous operation. Why would it do that?

Because I made a small error.

It should be

 if dcount("coursename","[FONT=Arial Narrow][SIZE=3]tblschoolclasses", "coursename = ' "& strname & "'") =  0 then      [/SIZE][/FONT]
 
[FONT=Arial Narrow][SIZE=3]msgbox "CourseName not in table"[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=3] include code to add coursename to table[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=3]End if[/SIZE][/FONT]

The error 424 was because Access didn't know which record you were looking at

 

[Rabbie]

possibly because the expression appears to be slightly incorrect

try

if dcount("*","tblschoolclasses", "coursename = " & chr(34) & strname & chr(34)) = 0  then 'No records found
  include code to add coursename to table
End if

assuming the field coursename is a field in the table tblschoolclasses, of type text

Thanks for the correction, Gemma. Your code should work

 

[Lrn2Code]

Thanks for the correction, Gemma. Your code should work

This does work - thank you so much! Now I've just got to figure out how to get rid of the 0 error I keep getting.

I really appreciate this site - you are so helpful and extremely quick with answers.

This stuff is still confusing to me (have lots to learn!) and I get all wrapped around the axle (so to "type") and can't figure out why I'm getting certain errors.

Bless you!

 

[Cosmoman]

I'm having a bit of the same issue here. I have a table called Expire with a field called Date. Now I was trying to get my splash page to open the main form if the current date has not reached the date in the Expired table. If it has then it gives a message and closed the database. So far I have this but I think i'm messing something up:

Private Sub Form_Load()
Dim intReply As Integer

If tblExpire.Date = Now() Then
DoCmd.Close acForm, Me.Name, acSaveNo
intReply = MsgBox("Project Has Expired")
ElseIf tblExpire.Date > Now() Then
DoCmd.Close acForm, Me.Name, acSaveNo
intReply = MsgBox("Project Has Expired")
Else
DoCmd.OpenForm "Form1"
End If
End Sub