order form in sample databases

[shutzy]

just asking if anyone has come accross a good order form in the samples. ive search them but you can only open so many before you tire. im looking for a order form, by that i mean for me the user to add items to a list by either dbl clicking or by clicking a button.

the order form is for booking multiple treatments in 1 appointment. i would like to be able to select several of my items(treatments) and they display in a list on the same for. kinda like a shopping basket where you keep adding until you have what you want.

im looking at the samples for inspiration as i just cant get my head around how the tbls and forms should be set up.

all i want to do at the moment is store that info in a tbl and lead on to the next step.

thanks for any help

 

[Jeanette Cunningham]

Create a list box with the possible treatments.
Set the listbox's Multi Select property to simple and that will allow you to select several treatments at once.

 

[shutzy]

thanks for the response. just one quesion. how do i get it from my list box to the table. i dont know what table properties i need. i dont know how to get multiple items into one field. i know about multiple look-ups but not sure if it would accept it from a multi select list box.

 

[Mr. B]

how do i get it from my list box to the table. i dont know what table properties i need. i dont know how to get multiple items into one field. i know about multiple look-ups but not sure if it would accept it from a multi select list box.

When you set the Multi Select property of a listbox to anything except "None" then to get the values that have been selected in the listbox you have to use VBA code to iterate through the SelectedItems of the control.

As your code is evaluating each selected item in the listbox control, you can use a DAO recordset to add each new record to the table.

You would not put multiple items in one field. You would create multiple records; one for each item selected.

If you have a many-to-many relationship (which it sounds like you have because many appointments can have many treatments) you always need a "join" table. This means that you would have a table of Clients and a table intensifying the appointments. You would also have a table of treatments. Then if you have multiple treatments for one appointment, you create a "join" table that will only hold the Appointment record Id and the Treatment record ID and create a record in this table for each treatment that is needed for each appointment.

 

[gemma-the-husky]

just asking if anyone has come accross a good order form in the samples. ive search them but you can only open so many before you tire. im looking for a order form, by that i mean for me the user to add items to a list by either dbl clicking or by clicking a button.

the order form is for booking multiple treatments in 1 appointment. i would like to be able to select several of my items(treatments) and they display in a list on the same for. kinda like a shopping basket where you keep adding until you have what you want.

im looking at the samples for inspiration as i just cant get my head around how the tbls and forms should be set up.

all i want to do at the moment is store that info in a tbl and lead on to the next step.

thanks for any help

before designing a form, you need to be sure your data tables are designed correctly

i presume you should have the follwing tables

clients table
appointments table (with clientid as foreign key)
treatmentvisit table (with appointmentid as a foreign key)

you then design a form
main form - client form
sub form - apponitments (per client)
sub-sub form - treatments (per appointment)

and you need to decide whether each of these forms are single-view, continuous view, or datasheet view.

this form/sub/sub may need a bit of playing with to get right - but it's your data tables that are the key. If they are not set up correctly, you won't get the forms set up correctly easily. either

 

[shutzy]

mr. b and gemma thanks for the informed advise. i do have tblClientDetails, tblTreatmentsList & tblClientTreatments.

the thing that i am having trouble to grasp is the table set up.gemma is extremely right in that if you aint got the right tables your forms are very much harder to create. that is exactly were my head breaks down. i do like to do as much as i can on my own but i am really struggling( i think ) on the tables.

let me have a go.

my 'join table' has fields - TreatmentID(primary key) / ClientDetailsID / TreatmentListID / DateOfTreatment / TimeOfTreatment / EmployeeID / PricePaid(this is because as i change the treatment price over the years i dont want the history to change, hope this is correct).

gemma, you did mention that i need a treatment visit tbl. would this be for when the customer has had the treatment? ie the tblClientTreatments is schduled appointments and the treatment visit would be completed appointments.

also gemma i know that you have mentioned that it costs £x because of the complexity. maybe this is my limit until i gain further experience and understand more.

thanks

 

[Mr. B]

Here is a demo database that will show a way to manage the many-to-many situations and also maintain complete control of what the user can do and when they can do it.

There is a lot of VBA code but hopefully it might help you to see another way of accomplishing what you want to do.

There is a demo in this database for selecting multiple items from lists and then adding or removing them from a table. There is also a demo of have a list display only items not already saved to the table and shown in another list.

 

[shutzy]

thanks mr b. you wernt joking when you said that there was alot of vba. ive notice how you have used one form for 2 different purposes. its good to see vba on a bigger scale than i have before. this will help me so much in understanding vba and tbl structure.

thanks again

 

[shutzy]

hi mr. b. i am trying to get to grips with what you have done within the form on the sample you provided. i hope you dont mind but i am trying to integrate it to my database as i like the functionality of it. it does everything i want but it belongs to different tables. so i am trying to convert it andby eck am i learning alot.

got a question. ive noticed that you have no queries. but i do notice that you have something that i would normally associate with quieries. this code:

SELECT tblPoItems.PoItemID, tblItems.ItemName, tblPoItems.Po FROM tblItems INNER JOIN tblPoItems ON tblItems.ItemID = tblPoItems.ItemID WHERE (((tblPoItems.Po)=[Forms]![frmManagePOs]![lstPos])) ORDER BY tblItems.ItemName;

this is from lstItemOnPO on the form. i had no idea that you could input this kind of thing into something like the row source. is this an alternative to a querie. i dont mean that it can replace all queries but in this case would a querie do the same job.

im not trying to change anything so please dont think that i am heading down the wrong path, just trying to understand what is what in this one particular form.

thanks

 

[Mr. B]

Yes, the sql statement you are seeing as the Record Source is exactly the same thing as a query. If you have a query, you can simply use the down arrow at the end of the property box and select your query. In fact, if you click the button with the three dots at the end of the property box, you will see the QBE presented just exactly as it would when you are creating a query.

Feel free to change anything you want. I just put the demo together to try to show you how you would do what you wanted to do.