Check 2 fields before running a query

[snicker]

I would like to check two different fields in a record. If both fields are a match I would like to run an update query. If either field is not a match I would like to run an append query. Example:

On update of a form control I would like to check the [ApDate] field for a specific date (say 10/10/2003) and the [Aptime] field for a specific time (say 8:00 AM).

If these 2 fields are in one record I want to update this record.

If these 2 fields are not in one record I want to append a new record.

I would like to do this with macros (Im not very good with VB). I thought maybe I could use DLookUp() But I dont know how to check for the second criteria. I have the Queries written I just need help on Checking the two criteria (the [ApDate] and [ApTime] fields). Any sugestions?

 

[Jack Cowley]

Something like this:

If Not IsNull(DLookup("[RecordID]", "YourTableName", "[RecordID] = " & Me.RecordID & " And Me.ApDate = #10/23/2003# And Me.ApTime = #8:00AM#")) Then
Run Update Query here
Else
Run Append Query here
End if

If you are going to use fields on your form for entry of the date and time modify the DLookup to look like this:

If Not IsNull(DLookup("[RecordID]", "YourTableName", "[RecordID] = " & Me.RecordID & " And Me.ApDate = #" & Me.NameOf ControlOnForm & #" And Me.ApTime = #" & Me.NameOfControlOnForm & "#")) Then

hth,
Jack

 

[snicker]

Thit is the code I'm using:

Private Sub SpCl800_AfterUpdate()
If Not IsNull(DLookup("[ApKey]", "Apointments", "[ApKey] = " & Me.ApKey & " And Me.ApDate = #" & Me.cboDate & # " And Me.ApTime = #8:00AM#")) Then
DoCmd.RunMacro "Appoint.Update800"
Else
DoCmd.RunMacro "Appoint.Append800"
End If

End Sub

I get an Expected Expression Error on the # sign in the code.

Here are some facts on the database.
[ApKey] is the primary key for the table Apointments. This is the table I want to edit.
The form is bound to a completely different table.
I will be searching for 8:00AM only.
The Date will come from [Forms]![Appointments]![cboDate]

 

[WayneRyan]

snicker,

Move the underlined # right so that it is inside the quotes and
you'll be OK.

Wayne

 

[Jack Cowley]

Wayne has it right, your syntax is wrong. This is correct:

If Not IsNull(DLookup("[ApKey]", "Apointments", "[ApKey] = " & Me.ApKey & " And Me.ApDate = #" & Me.cboDate & "# And Me.ApTime = #8:00AM#")) Then

Jack

 

[snicker]

cool that worked for that, but now I'm getting a "Compile error. method or data member not found." message. The debuger opens and has the Bold highlighted.

Private Sub SpCl800_AfterUpdate()
If Not IsNull(DLookup("[ApKey]", "Apointments", "[ApKey] = " & Me.ApKey & " And Me.ApDay = #" & Me.cboDay & "# And Me.ApTime = #8:00AM#")) Then
DoCmd.RunMacro "Appoint.Update800"
Else
DoCmd.RunMacro "Appoint.Append800"
End If

End Sub

Even on enter I get a "method or data member not found" error. once I remove the code the form works fine.

 

[snicker]

If I use me.ApKey doesnt that mean that a control on the appointments form must be named ApKey?

 

[Jack Cowley]

You have that right! Replace Me.ApKey with the actual name of the control on your form that has the value you want for the ApKey criteria.

Sorry I didn't spell that out in my response....

Jack

 

[snicker]

oh I get it

I dont want to edit [ApKey], it is an auto number. Do I need that statment in there? Can I just look for the [ApDate] and the [Aptime]? Or does the presence of the [ApKey] make the code search for [ApDate] and [ApTime] in the same record? Would something like this work:
____________________________________________________
Private Sub SpCl800_AfterUpdate()
If Not IsNull(DLookup("[ApTime] & [ApDate]", "Apointments", "[ApDate] = #" & Me.cboDay & "# And [ApTime] = #8:00AM#")) Then
DoCmd.RunMacro "Appoint.Update800"
Else
DoCmd.RunMacro "Appoint.Append800"
End If

End Sub
____________________________________________________

I know this code doesnt work (as I said I'm not very good with VB and generaly trip all over the punctuation). But would something like this work?

P.S. Thanks for your time and patience with a VB Newbe

 

[Jack Cowley]

snicker -

DLookup will not make any changes to your table. It is merely trying to find a record that meets the criteria and if it does the code returns 'true' and runs the code right below the If statement. If it returns 'false' (the record does not exist) then it does the code in the Else statement...

Just use the code that I suggested and it will do no harm...

Glad that I am able to help....

Jack

 

[snicker]

The code gets hung up here ("[ApKey] = " & Me.ApKey &). ApKey is not on the current form I can't put it on the current form. It is not on the bound table. (me.apDate) isnt on the form either.

Question The statment me.anything.... Does the (Me.) stand for only form controls? Or will it apply to the table mentioned in the Dlookup() also, (Apointments).

 

[Jack Cowley]

Hmmm. The plot thickens... If you leave out the primary key in the DLookup() code then DLookup will return True for the first record it finds that meets that Date and Time criteria. Is this what you want or do you want to see if a specific record meets the criteria?

If you only want to find the first record (it could be any record in the table) that meets the time and date then add an unbound control to your form called ApDate for the user to enter a date. Then remove the code related to the Primary key of the table. (If the table does NOT have a primary key then you should add one.)

From Access Help:

"You can use the Me property in Visual Basic to refer to a form, report, (or to the form or report associated with a subform or subreport), or class module where Visual Basic code is currently running.

Setting

The Me property is available only by using Visual Basic and is read-only in all views.

Remarks

The Me property contains an object reference to the current form or report and is faster than a fully qualified object reference. For example, the following two code fragments refer to the value of the LastName control for the current record on the Employees form:

strLastName = Forms!Employees.LastName

strLastName = Me!LastName"

Jack

 

[snicker]

Hmmm. The plot thickens... If you leave out the primary key in the DLookup() code then DLookup will return True for the first record it finds that meets that Date and Time criteria. Is this what you want or do you want to see if a specific record meets the criteria?
Jack

Thicker than silly puddy!!!

Yes. that is what I want to do. But I can't get the statement right. Can you help me with the syntax?

 

[Jack Cowley]

Try this:

If Not IsNull(DLookup("[ApDate]", "Apointments", "[ApDate] = #" & Me.cboDate & #" And Me.ApTime = #" & Me.ApTime & "#")) Then
DoCmd.RunMacro "Appoint.Update800"
Else
DoCmd.RunMacro "Appoint.Append800"
End If

I have assumed you have an unbound control on your form named ApTime for entry of the time you are looking for... If you want to use 8:00AM as a constant then scroll back and see how that code looks... And I think you will find this article helpful...

Jack

 

[snicker]

Jack,
I keep getting errors. Can you tell me where I can find an artical on VBA puncuation? I think this is my problem. I'm reading the artical from your last post and its not saying much about the "#" sign.

I Guess its time to learn VBA

 

[Jack Cowley]

You don't say what errors you are getting so give this a go...

If Not IsNull(DLookup("[ApDate]", "Apointments", "[ApDate] = '" & Me.cboDate & "' And Me.ApTime = '" & Me.ApTime & "'")) Then
DoCmd.RunMacro "Appoint.Update800"
Else
DoCmd.RunMacro "Appoint.Append800"
End If

hth,
Jack

 

[snicker]

The error I get is:
Compile error:
Method or data member not found:

Then the Debugger opens to this:
If Not IsNull(DLookup("[ApDate]", "Apointments", "[ApDate] = '" & Me.cboDay & "' And Me.ApTime = '" & Me.ApTime & "'")) Then
DoCmd.RunMacro "Appoint.Update800"
Else
DoCmd.RunMacro "Appoint.Append800"
End If
With the Bold representing the highlighted text. I have double and tripple checked the name of cboDay, the name is correct.

The other thing is Me.ApTime doesnt exist. I tried to use
... And [ApTime] = '" #8:00:00 AM# "'")) Then
And I get a syntax error.
*The expression may not result in the name of a macro, user-defined function and [eventprocedure].
*There may have been an error evaluating an event, function or macro.

If I use:
And [ApTime] = '" & #8:00:00 AM# & "'")) Then
I get a runtime error '3464'
Data type mismatch in criteria expression.

If it would make things easier I will email the db to you.

 

[Jon K]

Try:

If Not IsNull(DLookup("[ApDate]", "Apointments", "[ApDate] = #" & Me.cboDay & "# And [ApTime] = #" & Me.ApTime & "#")) Then


The Rule:-

The delimiter for a date/time field is the #
The delimiter for a text field is the ' (when put inside double quotes)
No delimiters for a numeric field

 

[Jack Cowley]

I am sure you have triple checked the Combo but something is wrong if Access does not reconize it. Delete the combo and create a new one using a new name. Then change the code to refect the new name. If you are going to use 8:00AM then the code should read:

If Not IsNull(DLookup("[ApTime] & [ApDate]", "Apointments", "[ApDate] = '" & Me.cboDay & "' And [ApTime] = #8:00:00AM#")) Then
DoCmd.RunMacro "Appoint.Update800"
Else
DoCmd.RunMacro "Appoint.Append800"
End If

I will cross my fingers for you.. Did I send you this article earlier?

Jack

 

[snicker]

Yes I read that article and it helped me come to this conclusion.

This is the code I got to work:
__________________________________________________
Private Sub SpCl800_AfterUpdate()
If (Not IsNull(DLookup("[ApDate]", "[Apointments]", "[ApDate]=[Forms]![Appointments]![cboDay]And [ApTime]= #8:00:00AM#"))) Then
DoCmd.RunMacro "appoint.TestUpdate"
Else: DoCmd.RunMacro "appoint.TestAppend"
End If
End Sub
__________________________________________________
Note: Access 2000

I noticed Jon K's post said Access 97 so I used the artical you sent me (and the code examples) and got a macro to work. I then converted the macro to Visual Basic.

I think my DB is written in 2000. I have Office XP. There was very little difference between the code you and Jon K supplied, and the code Access Supplied. Is this because Im working with Access 2000?

Once again, thank you to all who replied to this post.