Need Help Understanding How To Make This Work

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.

Re: 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

Re: Need Help Understanding How To Make This Work

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

Re: Need Help Understanding How To Make This Work

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).

Re: Need Help Understanding How To Make This Work

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.