if statement on afterupdate

[Styrker]

Hey folks,

Here is what I am trying to do. I have two tables one that stores color numbers (color) and one that stores jobs (jobs). I have 5 text boxes in the Jobs form that I need to reconcile against the color table. If the color number does not exist then I need to open the color form. Here is what I have currently but it does not work. As always any help is greatly appreciated:

on the first text box in the Afterupdate property for the Jobs form:

if me![text14]<>[color}![colornumber] then docmd.openform &quot;color&quot;

 

[thornmastr]

I think what you want is the following.

Dim strsql as string
Dim rs as recordset
Dim rsknt as integer

Strsql = “Select colornumber from color where colornumber = “ & me.text14.value
Set rs = currentdb.openrecordset(strsql, dbopensnapshot)
Rsknt = rs.recordcount
Rs.close
Set rs = nothing

If rsknt = 0 then
Docmd.openform “color”
Endif
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Styrker]

Well I tried your code and received a compile error on the

Strsql = “Select colornumber from color where colornumber = “ & me.text14.value

Line. The compile error was due to syntax.

 

[thornmastr]

I see why. I was under the impression that colornumber is numeric….but obviously me.text14.value is a string. You will have to make it numeric to work with this statement. Then it will compile. If colornumber is text and not numeric, then what you will have is

….where colornumber = ‘” & me.text14.value & “’” since string comparisons must be quoted.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Styrker]

Ok fixed the previous error but am now erceiving an Invalid Argument on the following line

Set rs = CurrentDb.openrecordset(strsql, dbopensnapshot)

Any help is appreciated.

Blaine

 

[thornmastr]

You are probably referencing ADO and not DAO if you are using A2K or above. Set a reference for the DAO library.

You might want to replace
Dim rs as recordset with
Dim rs as DAO.recordset you might want to make the following changes as well.

Remember to watch your text/number conversions.

I think what you want is the following.

Dim strsql as string
Dim rs as DAO.recordset
Dim db as DAO.database
Dim rsknt as integer

Strsql = “Select colornumber from color where colornumber = “ & me.text14.value
Set db = currentdb
Set rs =db.openrecordset(strsql, dbopensnapshot)
Rsknt = rs.recordcount
Rs.close
Set rs = nothing

If rsknt = 0 then
Docmd.openform “color”
Endif

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Styrker]

Well here is the code

Dim strsql As String
Dim rs As DAO.Recordset
Dim db As DAO.database
Dim rsknt As Integer

Strsql = “Select colornumber from color where colornumber = &quot; &quot;& me.text14.value&quot;
Set db = CurrentDb
Set rs = db.openrecordset(strsql, dbopensnapshot)
rsknt = rs.RecordCount
rs.Close
Set rs = Nothing

If rsknt = 0 Then
DoCmd.OpenForm “color”
End If

I am getting a expected endo of statement on the strsql= line at the first colornumber and an user defined type not defined error on the rs As DAO.Recordset line.

Sorry that I am making this difficult.

Blaine

 

[thornmastr]

Blaine,

The only problem remaining is strsql. I think one of the problems is the way TEK TIPS shows you single quotes and double quotes. I’m going to try and make this abit easier to understand. For a single quotoe I will use the abbreviation SQ. for a double Quote the abbreviation DQ.

If I’m making this worse, take it to private email and send your questions direct.

This is for when colornumber is represented as a string value.

Strsql = DQSelect colornumber from color where colornumber = SQDQ & me.text14.value & DQSQDQ
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Styrker]

Here is what I have so far the only problem is the strsql line, explained below:

Private Sub Text14_AfterUpdate()

Dim strsql As String
Dim rs As Recordset
Dim rsknt As Integer

Strsql = “Select colornumber from color where colornumber = ' &quot;& me.text14.value&quot;'&quot;
Set db = CurrentDb
Set rs = db.openrecordset(strsql, dbopensnapshot)
rsknt = rs.RecordCount
rs.Close
Set rs = Nothing

If rsknt = 0 Then
DoCmd.OpenForm “color”
End If



End Sub

everything seems to be happy until i leave the strsql line and then I get the expected end of statement at the following point

Strsql = “Select colornumber

Thanks again for all of your help in advance.

Blaine

 

[thornmastr]

Strsql = “Select colornumber from color where colornumber = ' &quot;& me.text14.value&quot;'&quot;



Almost there.

Put a blank between “and &, so
…..where colornumber = ‘”&me becomes
…..where colornumber = ‘” & me

change

…… me.text14.value&quot;'&quot; to
me.text14.value & &quot;'&quot;

You’ve got it.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[Styrker]

So close yet so far away: Here is the strsql line

Strsql = “Select colornumber from color where colornumber ='&quot; & me.text14.value & &quot;'&quot;

I am still getting the &quot;expected end of statement&quot; compile error at Strsql = “Select colornumber

Thanks again for all of your help. At least I am understanding what we are doing.

Blaine

 

[thornmastr]

Blaine,

Can you zip your database and email it (also tell me what version of Access you are using).

Thanks.

Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com