help with filters in a form

[scottappleford]

Hi

I am trying to open a form when I untick the yes/no box and then to find the record I was working on in the original form.

I would also like to open the form from the switchboard with a command button but acess to ask what record I am looking for, like a query does when you use [please enter....]

Really need some help here.

Thanks in adavance.

scott

 

[ColinEssex]

Hi

1) why do you want to have the same detail on a second form when its already open on the first form?


2) You could use an InputBox and RecordSetClone to find the record from the switchboard

Col

 

[scottappleford]

hi

What I am trying to do is have a form with the main details with the box ticked to say the box is in storage. when the box is unticked then the box is going out of storage, however i then need to fill in additional informationon where the box is going who requested it with dates. I would like another form to open when i untick the box and the form to open with that record found rather than the user have to find the record. I am using the contract no. from the main form and the location details as a subform because it is a seperate table. Hope this helps.

Also the command button from the switchboard is when the box comes back, users will press this and a message box pops up asking for the contract no. and then opens the form mentioned above with the record so users again do not have to find it, ideally i would prefer it so that users can only access and see that one record.

your is appreciated, I hope that i am making sense and doing things right?

scott

 

[ColinEssex]

Ok, I think I've got what you mean.

You have a main form with details of the box. This form is bound to the "box" table. A box can then go to many destinations, therefore you need a BoxDestination table. This will have an IDNo field and whatever else you need. You will also have a form for the same called "Destination".

You then need to create a query called "Filter_Box_Destinations", put in the BoxDestination table and select one field, the IDNo field (which is the same as the IDNo field in the Box table)

Next, create a macro called "Apply filter to boxes" and in the actions bit, select ApplyFilter, in the filter name bit, select the query you created.

In the OnLoad property of the "Destination" form enter the macro name. In the filter property of that form put this

((((BoxDestination.ID_No)=[forms]![Boxes]![id_no])))

note... your table and form names may be different of course.

Now when you open the destination form from the main Boxes form it'll have the IDNo ready for you to input the rest of the data.

You can open this form with the AfterUpdate event of your TickBox.

I think its best you get this lot working before we tackle the other problem.!!!

Good Luck
Col

 

[scottappleford]

hi again

I hope you have patience?

I have tried that but it does not work, must be me

OK, each box belongs to a contract, so i have the following tbls

tbl 1

contract no PK
contract details etc...

tbl 2

Contract no pk
box no PK
yes/no

tbl 3

Contract no pk
box no pk
item no pk
box contents

tbl 4

contract no
box no
location
who requested the box etc...

tbl 1 is linked to tbl 2 by contract no, tbl2 is linked to tbl 3 by contract no and box no. And tbl 2 is alos linked to tbl 4 by contract no and box no.

Now each contract can have as many boxes as it wants, however each box can only contain upto ten items. e.g. contract 601650 has ten boxes and boxes 1-9 are full with tem items and box 10 has only 3 itmes.

So what I done was have tbl 1 as a form with tbl 2 and 3 as seperate sub forms.

Now when a box leaves the archive I untick the box in the tbl 2 which is a sub form of the tbl 1 form..

The destination frm has tbl 2 as the main form with tbl 4 as the sub form.

When I open the location form from the main form tbl 1 form with the 2 sub forms I would like to have the contract no/box no that i was working on showing on the destination form as i only have to fill out the dates etc and off it goes.

The second problem of getting the form to open from a button to book in back in and find the record with a parameter query can wait.


appologies and thanks..

scott

 

[ColinEssex]

Hi Scott

Where did the items come from? you never mentioned them before - from your first and second posts, I assumed there was just one box allocated to a customer

So whats all this items malarkey then? can different customers have different items from the same box?.............totally confused now......sorry

I know you went into detail in your last post but I think you might need to explain what these boxes and items are perhaps.

Col