By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Not Sure How This Can Be Done

P: n/a
**********
Situation
**********
I have a table which contains two text fields which represents the first
and last name of clients. A third field in each record holds the text
representing an AgentID, and is null unless and until a user selects the
record for editing (via a form described next). Now, I have a parameter
form into which the user enters a first and last name, and a button on
that form whose button click event runs a macro which executes VBA code
to run a filter (query) to determine 1) whether any records match on the
first and last name selected, using only the first and last name as
criteria; and, if so, then 2) whether the AgentID field is null or isn't
null (the latter being the case because another agent or the Current
User has previously selected the record for editing). If the AgentID
field Is Null, the code places the value of CurrentUser() in the AgentID
field in a form in edit mode. This is accomplished by checking to see
whether or not the record count of the recordset returned by another
query which filters the table using the first and last name and
AgentID=Is Not Null as criteria is zero or not. If the recordset count
is zero, then the AgentID=Is Null and the record can be edited. The
pseudo code that checks for record count looks like this:

me.RecordSource = "my sql goes here"

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

*********
Problem
*********
Now, here is where I have a problem which I can't seem to figure out.
What if instead of there being only one record in the table which
matches first and last name with AgentID=Is Null, there are several
records returned? In that senerio the fact that the

me.RecordSetClone.RecordCount=0

occurs means there is at least one record in the me.RecordSetClone, not
just one.

*******************
Here's My Question
*******************
My question to anyone who thinks they can help me with this is: Once
the condition me.RecordSetClone.RecordCount=0 is met, if I display the
records within the record set to the user and let the user select the
record he/she wants to edit, how do I then requery the original
recordset to return only a single record, i.e. the one the user
selected, for editing?

********************
Proposed Solution
********************
I'm thinking I could employ a boolean field in the table, call it
"selected," which would appear in a form which displays multiple records
returned by the original recordset, then when the user clicks on the
checkbox and clicks a button on the form, button click event code would
be activated to requery the recordset with the addition of
selected.value=1 as a filter criteria, thereby returning only that
record in another record set. Does this sound plausable, or is there
perhaps a better way to do this?

Thanking anyone in advance for a reply.
Feb 7 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Donald Calloway" <dr********@verizon.netwrote in message
I looked at your explanation and the links you provided, but I'm a
little confused about one point. In what event is the VBA code,
Use whatever event suits, e.g. the Click event of a command button.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Feb 8 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.