Need Help Understanding How To Make This Work

Donald Calloway wrote in
news:lnvnj.7230$v86.6825@trnddc08:

> I have created a database application that uses a macro, activated
> by a button click event, that runs a query which returns a
> recordset consisting of a subset of the matching database record,
> and displays the data within a form. The query on the table
> produces a recordset based on the matching criteria consisting of
> a person's first and last name, or fields ClientFirstname and
> ClientLastname, respectively. I've programmed the application to
> display the recordset in the form only if a third field,
> AgentName, in the record has a null value. If the AgentName field
> is not null, then no record is displayed because the query will
> return a null recordset.
>
> Now, with this backdrop, here is my goal: I'm trying to determine
> the VBA code that would determine whether a recordset returned by
> a query is null or not. Then, if the recordset is null, I want to
> have other VBA code run an another query to yield the same subset
> of data fields and display them in a replica of the first form,
> but this time, I want to display the actual value of the AgentName
> field along with the other data in the record as defined by the
> form. I hope this is clear.
>
> Essentially, what I need to know this: what is the syntax of VBA
> code that can be used to determine whether or not a recordset
> returned by a query is null or not null? Thanking anyone in
> advance for their help with this problem.
>
Why go to all that trouble?

Code the onClick event to open the form directly, with the first and
last name criteria, and set a filter on the form to show only the
records with null agent_name. If the form's recordset is empty,
based on the simple test of recordcount being 0, then remove the
filter.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

The only question is where/when/how are you using this query?

Are you assigning this query to a report, form, reocrrdset?????

just go:

me.RecordSource = "my sql goes here"

if me.RecordSetClone.RecordCount = 0 then
' no records returned
' do whatever here when reocrds are not found
end else
' there are records....
' do whatever here when records are found
end if


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com

Albert D. Kallal wrote:
> The only question is where/when/how are you using this query?
>
> Are you assigning this query to a report, form, reocrrdset?????
>
> just go:
>
> me.RecordSource = "my sql goes here"
>
> if me.RecordSetClone.RecordCount = 0 then
> ' no records returned
> ' do whatever here when reocrds are not found
> end else
> ' there are records....
> ' do whatever here when records are found
> end if
>
>
My RecordSource is a query that looks for a record that matches the
ClientFirstname and ClientLastname fields entered from a parameter
dialog box, AND where AgentName field is null (i.e., no agent has been
assigned to this client's record yet). If the returned recordset is
null (i.e., an agent has been assigned to the client already), I want to
have my code open a replica form (DoCmd.RunMacro "macroname") to display
the data to the user in read-only mode, else, the AgentName field
=CurrentUser() (i.e., the User gets assigned as the agent for the client).

Donald Calloway wrote:
> Albert D. Kallal wrote:
>> The only question is where/when/how are you using this query?
>>
>> Are you assigning this query to a report, form, reocrrdset?????
>>
>> just go:
>>
>> me.RecordSource = "my sql goes here"
>>
>> if me.RecordSetClone.RecordCount = 0 then
>> ' no records returned
>> ' do whatever here when reocrds are not found
>> end else
>> ' there are records....
>> ' do whatever here when records are found
>> end if
>>
>>
> My RecordSource is a query that looks for a record that matches the
> ClientFirstname and ClientLastname fields entered from a parameter
> dialog box, AND where AgentName field is null (i.e., no agent has been
> assigned to this client's record yet). If the returned recordset is
> null (i.e., an agent has been assigned to the client already), I want to
> have my code open a replica form (DoCmd.RunMacro "macroname") to display
> the data to the user in read-only mode, else, the AgentName field
> =CurrentUser() (i.e., the User gets assigned as the agent for the client).

That worked, Mr. Kallal. Thanks for your suggestion.

Bob Quintal wrote:
> Donald Calloway wrote in
> news:lnvnj.7230$v86.6825@trnddc08:
>
>> I have created a database application that uses a macro, activated
>> by a button click event, that runs a query which returns a
>> recordset consisting of a subset of the matching database record,
>> and displays the data within a form. The query on the table
>> produces a recordset based on the matching criteria consisting of
>> a person's first and last name, or fields ClientFirstname and
>> ClientLastname, respectively. I've programmed the application to
>> display the recordset in the form only if a third field,
>> AgentName, in the record has a null value. If the AgentName field
>> is not null, then no record is displayed because the query will
>> return a null recordset.
>>
>> Now, with this backdrop, here is my goal: I'm trying to determine
>> the VBA code that would determine whether a recordset returned by
>> a query is null or not. Then, if the recordset is null, I want to
>> have other VBA code run an another query to yield the same subset
>> of data fields and display them in a replica of the first form,
>> but this time, I want to display the actual value of the AgentName
>> field along with the other data in the record as defined by the
>> form. I hope this is clear.
>>
>> Essentially, what I need to know this: what is the syntax of VBA
>> code that can be used to determine whether or not a recordset
>> returned by a query is null or not null? Thanking anyone in
>> advance for their help with this problem.
>>
> Why go to all that trouble?
>
> Code the onClick event to open the form directly, with the first and
> last name criteria, and set a filter on the form to show only the
> records with null agent_name. If the form's recordset is empty,
> based on the simple test of recordcount being 0, then remove the
> filter.
>
Thank you for your suggestion. I had tried that before, but it turns
out what I needed was a little more complicated than that. In my
program, I have to look for several criteria to decide what I need the
program to do. A sequence of If statements did the trick for me. In my
program, I must first look to see if a record exists that matches the
Firstname and Lastname of a client; then, I must look to see if an Agent
is assigned to the client already. If an agent isn't assigned, I open
one form, if an Agent is assigned, I must open another form in Read-Only
mode. Here was the resulting code I used:

'Does record with client's name exist in the database?
Me.RecordSource = "SELECT [Mail Log].ClientFirstname, [Mail
Log].ClientLastname FROM [Mail Log] WHERE ((([Mail
Log].ClientFirstname)=[Forms]![Mail Document Search]![ClientFirstname])
AND (([Mail Log].ClientLastname)=[Forms]![Mail Document
Search]![ClientLastname]));"
If Me.RecordsetClone.RecordCount = 0 Then
'(False) No matching record for client's name
MsgBox "No matching record for client's name entered. Click OK
now to search for client's name."
DoCmd.RunMacro "Mail Document Search (Agents)"
Else
'(True) Matching record exists. Now,
'Is AgentName = CurrentUser()?
Me.RecordSource = "SELECT [Mail Log].ClientFirstname, [Mail
Log].ClientLastname, [Mail Log].AgentName FROM [Mail Log] WHERE ((([Mail
Log].ClientFirstname)=[Forms]![Mail Document Search]![ClientFirstname])
AND (([Mail Log].ClientLastname)=[Forms]![Mail Document
Search]![ClientLastname]) AND (([Mail Log].AgentName)=CurrentUser()));"
If Me.RecordsetClone.RecordCount <> 0 Then
'(True) AgentName = CurrentUser()
MsgBox "You are the assigned agent for this document. Click
OK to enter Mail Production Edit Mode"
DoCmd.RunMacro "Mail Not Processed"
Else
'(False) AgentName <> CurrentUser()
'MsgBox "Agent's name is either blank or is another agent."
' Is AgentName = Null?
Me.RecordSource = "SELECT [Mail Log].ClientFirstname, [Mail
Log].ClientLastname, [Mail Log].AgentName FROM [Mail Log] WHERE ((([Mail
Log].ClientFirstname)=[Forms]![Mail Document Search]![ClientFirstname])
AND (([Mail Log].ClientLastname)=[Forms]![Mail Document
Search]![ClientLastname]) AND (([Mail Log].AgentName) Is Null));"
If Me.RecordsetClone.RecordCount = 0 Then
'(False) AgentName is another agent
MsgBox "You are not the assigned agent for this document.
Click OK to view Mail Production in View-Only Mode."
DoCmd.RunMacro "MailDocumentAlreadyAssigned"
Else
'(True) AgentName is null
MsgBox "When you click the OK button, you will be the
agent assigned to this document in Mail Prodution Edit Mode"
DoCmd.RunMacro "Mail Not Processed"
End If
End If
End If

This did the trick. Thanks again for your suggestions.