Substitute variable for Recordset name 2

[ronphx]

I have two recordsets which represent tables - rsmaster and rsship.

the recordset rsship refers to a table (tblship) which may or may not contain a ship to address for a customer that is different from the ship to address in rsmaster. If there is a record present in tblship, I want to transfer the field amounts to form variables. Otherwise I want to get the variables from rsmaster.

I am doing a select and recordcount on rsship to determine if a ship to address is present. I would like to avoid duplicating code by doing something like this:

if rsship.recordcount = 0 then
with rsmaser
else
with rsship
end if

me.txtshiptoname = .sname
.
.
end with

but that, of course, generates an error message. Does anyone know how I could substitute a variable for the recordset and do something like

if rsship.recordcount = 0 then
var = "rsmaster"
else
var = "rsship"
endif

with var
.
.
end with

Thanks.

 

[PHV]

Why not a single recordset based on an UNION query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RoyVidar]

There are probably several methods, one could be to create a third recordset and:

[tt]dim rs as dao.recordset
if rsship.recordcount = 0 then
set rs = rsmaser.clone
else
set rs =rsship.clone
end if
with rs
...[/tt]

Roy-Vidar

 

[ronphx]

Thanks to both of you. I tried both of the suggesstions, and both of them worked perfectly. I appreciate your prompt and helpful responses.