From Stock to Orders?

[David Tro]

Hello,
For my assignment I have to develop a db for a video hire firm.
What I was wondering was how would I manage to get a video from my stock sheet onto my orders sheet when I have clicked onto my selection of video.
I have messed around with one or two things to try to acheive this but have'nt had any success yet.
Anyone any ideas,tips,hints etc
Thanks
David

 

[ColinEssex]

I would have a "status" ComboBox field for each video.

In stock
Out of stock
Out on hire

etc

then base your queries to include the status. Although I suppose thinking about it you'll have more than one copy of each video so you'll need to have a stock level counter as well.

Col

 

[David Tro]

Hi Colin
I suppose this may sound silly but could you give me a step by step guide as how to go about this?
I have tried various methods including adding a combo box control etc but I still cant get things correct

Thanks
David

 

[ColinEssex]

You need to create a Status table masterfile. 2 fields - StatusID and StatusDesc.

Add a status field to your main table

Create a ComboBox with the wizard on your form. Source will be the status table and the bound field will be 1 and get the description to show in the ComboBox.

The reason to bind the 1st (ID) field is that you can then amend the description anytime and it will always show the up-to-date description for each record)

Col

 

[David Tro]

Hi Colin,
I think I know what you are getting at, the only thing a litle vague is what do you mean with Source will be the status table and bound field will be 1 and get the description to show in the combo box?
I will try as you suggested but things still a little unclear.
Thanks
David

 

[ColinEssex]

After you've created the status table. Go to your form and the ComboBox wizard will prompt you for the correct answers.

The bound field is the field that will get posted to the main table but you don't want that to show in the ComboBox as no-one will know what the code means, you want the description to show and the relevant code to go into the table.

Col

 

[burton]

you say use the status does this mean that it isn't able to book an item. Because i don't want to be able to book an item twice when it is already booked out. this is probably an important feature that needs to be included.

 

[David Tro]

Hello again,
After spending some fruitless time over the weekend trying to get this to work it seems I have developed a mental block with it.
Could you give me 5 minutes of your time and run through the steps required to get this particular need for my db to work.

Thank you
David Tro

 

[ColinEssex]

Post your Db and I'll have a look at it

Col

 

[David Tro]

Hi Colin,
Thanks for your offer to look at my db. I have tried to attach it but I got the message saying the file was too big even though I have zipped it.
Is there any other way I can send it to you?
Many thanks
David