Project Problems!

[Purity14]

Here is my problem i wish to overcome...
I have a numbers of swipecards that customers would purchase from me.
I wish to simplify the process..

cards are numbered 1 - 700

I wish to give a a certain customer cards numbered 33 - 60.

I would like some way of reporting that cards 33 - 60 have been removed from the 1 - 700 and to tell me what cards are remaining as well as cards missing that i need to order.

I can write in VB if that helps, but after a day of thinking about it my head is a mess *Grins*
But ideally id like to write something in VB that would check against a database...

I hope you understand and can offer a good explaination..

Regards,
P14

 

[Moniker]

Setup a table like this:

Card_ID   CheckedOut   Customer
1         No           (null)
2         Yes          Bob
3         No           (null)
4         Yes          CompanyA
5         Yes          CompanyA
.         .            .
.         .            .
X         X            X

This is not a full table, but the idea is to tie the card_ID to a customer, and carry a "CheckedOut" boolean field. You can then easily query this table to figure out which cards are out, which are in, who has what cards, etc.

 

[Purity14]

Sorry i should have been more explainitory..

I dont really need to know who i have sold the card to.

Just the number of the card... either being present or not present.

for example if there is 700 cards.. and i sell 213 to 566

i dont want to have to select 213 214 215 216 manually.
I wanted to create some kind of frontend/form where i can select something like: "greater than or equal to 213 but less than or equal to 566"

in the style of 2 combo boxes with the cards listed on each side..?

 

[Moniker]

Set a minimum card number and a maximum card number textbox that you enter into. Then, write a simple update query to check the "CheckedOut" field (or whatever you called it) for you.

CurrentDb.Execute "UPDATE YourTableName SET CheckedOut = True WHERE Card_ID >= " & MinValueTextboxValue & " AND Card_ID<= " & MaxValueTextboxValue & ";"

You can reverse that when the cards are checked back in:

CurrentDb.Execute "UPDATE YourTableName SET CheckedOut = False WHERE Card_ID >= " & MinValueTextboxValue & " AND Card_ID<= " & MaxValueTextboxValue & ";"

You can use comboboxes or listboxes or whatever. You just have to somehow set the minimum and maximum values.

That example is not exactly an elegant method. You could easily build two listboxes, one for "Not Checked Out" and one for "Checked Out":

lstNotCheckedOut.RecordSource = "SELECT Card_ID FROM YourTableName WHERE CheckedOut = False"
lstCheckedOut.RecordSource = "SELECT Card_ID FROM YourTableName WHERE CheckedOut = True"

Set the listboxes to multi-select extended in the properties window. Then, put command buttons on the form that are something like "Update Checked Out Cards" and "Update Returned Cards". Use the above code in the OnClick events of those buttons to check out/return the cards and after the SQL runs, refresh the listboxes to get their current status.

Loop through the listbox ItemSelected property to determine what items are checked in each listbox.