Selecting a Record in Datasheet View from Linked Table

Q

querykitty

I would like to create a "yes/no" field in a datasheet view that is based on
a linked table that I can not modify. Is there a way to create a temporary
table that I can pull into the query that could contain this "yes/no" field
without having to actually add a field to the original table?

Thanks!
 
S

SteveD

I'm sure there are a couple of ways, but here's at least one.

Run a make table query** that includes the primary key from your linked
table and a yes/no field. You can either set the field to Yes or No when it
is run. Assuming that you'll apply some logic and update the Yes/No field.
The next step is a query that has both tables linked by the Primary key and
use this to support your form, report, or datasheet view.

Hope this makes sense to you.
SteveD

** this could be a delete query and then an append query
 
Q

querykitty

Thanks Steve that worked! I put it in a macro, created the table and am now
having the form opening off of the new table. I've run into a small error
though. I get a warning message that tells me that it can't "lock" the table
I just created because it is in use. I put another line in the macro to make
sure the new table was "closed" before I opened the form, but am still
receiving the message. I also checked the properties of the form and they do
not indicate "record locked". I am not sure what else could be causing this
message.
 
S

SteveD

Initially I'm puzzled. Are you updating the new table with the form or just
displaying the data? I'll try and create an example and see if I get similar
results. If the fields your linking with are indexed, I would keep the "new"
table and add the index. I would create a delete query and change the Make
Table query to an Append query and add the delete query to the macro. I'll
get back to you shortly.

SteveD
 
Q

querykitty

I actually tried modifying the make table query to an append query and and
replaced the delete table with a delete query, but now I get another curious
error. When my form opens it shows "#deleted" across all the fields. I
don't "Open" the form until after the append query has run however and when I
go into the table I created it shows that one row appended. so I don't know
if my form needs to refresh? I did add an index to the table and that did
not seem to help.
 
Q

querykitty

Oh, forgot to reply to your question below. the only action I want to take
in the form is to be able to check the "yes/no" box to select that record. I
will be adding functionality to update fields in another table based on the
rows I select.
 
S

SteveD

Hi Qkitty,
Are you running the marco while the form is open?? I didn't state that the
macro (either maketable or del/append query) needs to be run before the form
is opened. If this form is being opened from a menu, run the macro first,
then open the form. Please let me know.

Thanks,
SteveD
 
Q

querykitty

I open the form from within the macro. first run the delete query, then the
append query and then open the form. Should I seperate these in VB Code
instead... run the macro and then open the form seperately?
 
S

SteveD

I created a sample here as follows: begin with all items closed --> the macro
1) setwarnings NO, open del query, open append query, open form, setwarnings
YES this seems to work OK.

Is there something else open that is connected to the new table?
 
Q

querykitty

I got it to work. I had to do a requery after I opened the form. We are
working in a Citrix environment (no so speedy) so my form was apparently
opening faster than my query was appending so I was getting the "#delete"
but when I requery after the form opens it populates correctly. Thanks so
much for your assistance, I'm very pleased with the end result.
 
S

SteveD

Very good!!

querykitty said:
I got it to work. I had to do a requery after I opened the form. We are
working in a Citrix environment (no so speedy) so my form was apparently
opening faster than my query was appending so I was getting the "#delete"
but when I requery after the form opens it populates correctly. Thanks so
much for your assistance, I'm very pleased with the end result.
Click to expand...
 
Top