Multi select records in a shared back end database (1 Viewer)

yeasir01 · Sep 26, 2019

Hello,

I plan to split and share my database with multiple users, however after I finished building the project I noticed that my checkbox are bound to a table so If one user checks the box the other user hits the print button some undesirable results will occur :banghead:.
Is there a better way to handle this? I know a list box is a solution, but I really would like to keep my continuous form. I also read about temp tables & local tables, but i'm not sure on how to set those up or if that would solve my problem. Any help would be appreciated.

Ranman256 · Sep 26, 2019

My users have a local db, printer.mdb, on c:\temp\.

In it, is their settings for their printer. A tblPrinter table.

So it's basically a local table linked into their front end.
When I update their FE, the settings are unaffected.

arnelgp · Sep 26, 2019

what is the checkbox for?
for inclusion/exclusion of records to print?
create a local table for the checkbox.
you only need to fields, the FK field (number), and the checkbox.
add all the PK from where this table is attached.
on your form you can Link this checkbox-table to your table.

on adding records to related table, add also record to
the checkbox-table.

theDBguy · Sep 26, 2019

Hi. I see two suggestions already made that basically say you need to move the data away from your backend. The other option, if you want to keep the data in the backend is to include some information about the user to avoid the conflicts you mentioned.

yeasir01 · Sep 26, 2019

arnelgp said:
what is the checkbox for?
for inclusion/exclusion of records to print?
create a local table for the checkbox.
you only need to fields, the FK field (number), and the checkbox.
add all the PK from where this table is attached.
on your form you can Link this checkbox-table to your table.

on adding records to related table, add also record to
the checkbox-table.

Yes your correct.

Assuming I had a local table with pk, fk & checkboxes, If I add a record I can have the local update, however if another user is adding records how will the update my local table? I’m not sure that would work, that’s where I’m stuck.

Gasman · Sep 26, 2019

The way I understood it, each user has their own table in the FE?

yeasir01 said:
Yes your correct.

Assuming I had a local table with pk, fk & checkboxes, If I add a record I can have the local update, however if another user is adding records how will the update my local table? I’m not sure that would work, that’s where I’m stuck.

arnelgp · Sep 26, 2019

gasman you're correct.
he need first to do the fix this on the Master FE.
then distribute it.

On second thought, if he Main table has addition, it would be
hard to maintain the checked table.

you may forget my approach, and instead add Transaction
to your form.

yeasir01 · Sep 26, 2019

Gasman said:
The way I understood it, each user has their own table in the FE?

The wouldnt work, would it. Because If user 1 adds a record to the shared BE the FE table of user 1 would update with new records, however if user 2 adds a new record it would not be avialbe to user 1 since the checkbox table resides on user 1's computer & not the shared BE.

If the checkbox sits in the BE then user 1 selecting a record would also select a record for user 2. I have attached a copy for better understanding.

Gasman · Sep 26, 2019

I cannot read the DB, I only have 2007.

I believe you would only add records for those you tick to print/mark with checkbox, then use that table to print the records, then presumably delete the records for the next time, or just clear the flag and insert only new ones each time?

I think you are getting confused with actual BE data records and FE 'print' records?

That is the way I understood it, but I get it wrong a fair bit.

yeasir01 · Sep 26, 2019

Gasman said:
I cannot read the DB, I only have 2007.

I believe you would only add records for those you tick to print/mark with checkbox, then use that table to print the records, then presumably delete the records for the next time, or just clear the flag and insert only new ones each time?

I think you are getting confused with actual BE data records and FE 'print' records?

That is the way I understood it, but I get it wrong a fair bit.

If I placed a unbound checkbox to update each record then all records would be set, therefore the checkbox has to be bound.

MajP · Sep 26, 2019

I would get rid of the Selected yes no field. Instead you need a table "tblSelected" on the front end.

Code:

tblSelected
  merchandiseID_FK

When you "select" it you write to the tblSelected. When you unselect you delete from the selected table. The selected checkbox is a calculated field returning true if the row is in the selection table. When you click on the control it writes or deletes from tblSelection.

Gasman · Sep 26, 2019

So go with theDBguy's suggestion and include the username and then filter for the username for whatever that checkbox is meant to handle.

yeasir01 · Sep 26, 2019

Gasman said:
So go with theDBguy's suggestion and include the username and then filter for the username for whatever that checkbox is meant to handle.

there is no user name it a simple 1 form database where anyone with access can add/edit records and print shelf tag labels.

yeasir01 · Sep 26, 2019

MajP said:
I would get rid of the Selected yes no field. Instead you need a table "tblSelected" on the front end.

Code:

tblSelected merchandiseID_FK

When you "select" it you write to the tblSelected. When you unselect you delete from the selected table. The selected checkbox is a calculated field returning true if the row is in the selection table. When you click on the control it writes or deletes from tblSelection.

What method do I use to select the record to add to the local tbl? I would like to keep the checkbox if possible.

CJ_London · Sep 26, 2019

you might want to take a look at the multi select form here

https://www.access-programmers.co.uk/forums/showthread.php?t=289116

it uses a button rather than a checkbox

the point is it collects the id's of the records selected in a control called SelectedList

you can reference this in your report by having a calculated control

eval(ID & " in (" & forms!myform!selectedlist & ")")

and the criteria set to true

yeasir01 · Sep 26, 2019

CJ_London said:
you might want to take a look at the multi select form here

https://www.access-programmers.co.uk/forums/showthread.php?t=289116

it uses a button rather than a checkbox

the point is it collects the id's of the records selected in a control called SelectedList

you can reference this in your report by having a calculated control

eval(ID & " in (" & forms!myform!selectedlist & ")")

and the criteria set to true

Thank you for bringing my attention to this thread, I just learned a new way to accomplish the desired goal. However is they're a way to make it work with check boxes instead?

CJ_London · Sep 26, 2019

you would need to set the controlsource for the checkbox to a calculation that results in true or false - something like

=instr(selectedlist, "|" & ID & "|")>0

then use the click event to populate/depopulate the SelectedList per the link I sent

Alternatively you can use a disconnected ado recordset which can use a checkbox

but the underlying principle for both methods remains the same - you create a string of ID's or a table as proposed by MajP

MajP · Sep 27, 2019

This seems the easiest. Make a table tblSelected.

Code:

TblSelected
 merchandiseID_FK
 Selected ' yes no field

When you load the form simply ensure you have all the IDs by doing an append query.
Do a left join in the form to this table. That is probably the simplest approach.

I believe this is what Arnelgp is suggesting so if this works for you make sure to give him credit. He tends to get his panties in a bunch when he does not get credit.

yeasir01 · Sep 27, 2019

CJ_London said:
you would need to set the controlsource for the checkbox to a calculation that results in true or false - something like

=instr(selectedlist, "|" & ID & "|")>0

then use the click event to populate/depopulate the SelectedList per the link I sent

Alternatively you can use a disconnected ado recordset which can use a checkbox

but the underlying principle for both methods remains the same - you create a string of ID's or a table as proposed by MajP

Sorry for the late response, Iv decided to go with the method you mentioned on the sample database. I Just have one question, how do I interpret this into usable data for query search (criteria)? The numbers below represent ID numbers produced by making a selection.

6|5|8|9|7|2|3|4|712|

As an example if I type the data below into a criteria section of the querry it yields the desired results.

If I type "6 or 5 or 8 or 9 or 7 or 2 or 3 or 4 or 712" it works to filter

CJ_London · Sep 27, 2019

how do I interpret this into usable data for query search

as I suggested

you can reference this in your report by having a calculated control

eval(ID & " in (" & forms!myform!selectedlist & ")")

and the criteria set to true

HOWEVER that is slightly wrong (I was thinking of something else), it should be

InStr("|" & [forms]![myform].[selectedlist],"|" & ID] & "|")>0

So your report query will look something like

SELECT *
FROM myTable
WHERE InStr("|" & [forms]![myform].[selectedlist],"|" & [ID] & "|")>0

you need to include the additional '|'s because if you didn't

if you were looking for ID's 1|6|168| and didn't include them around the ID field it would find any record with 1, 6 or 168 in the id
if you only included the one at the end, it would also return any record that ends in 1, 6 or 168
so given that requirement if you didn't include the one before your search it would not return the first record

Multi select records in a shared back end database (1 Viewer)

Registered User.

Well-known member

..forever waiting... waiting for jellybean!

I’m here to help

Registered User.

Enthusiastic Amateur

..forever waiting... waiting for jellybean!

Registered User.

Attachments

Enthusiastic Amateur

Registered User.

You've got your good things, and you've got mine.

Enthusiastic Amateur

Registered User.

Registered User.

Super Moderator

Registered User.

Super Moderator

You've got your good things, and you've got mine.

Attachments

Registered User.

Super Moderator

Similar threads

Users who are viewing this thread