table and form question

[ryan.fitzpatrick3]

I have a 6 tables all of which are sub tables of a bigger table, i.e.
1 goes into 2, 2 into 3, 3 into 4, etc down to 6, basically it's a
heirarchy of tables. Now I created a form where I can see all these
tables which I did through list boxes; when I click the first table
list box the selection that I picked in that listbox (table 1) brings
up the selections in list box 2 that pertain to table 1, and that goes
into listboxs for the rest of the tables. Now how can I can input new
data into table 5, in the form, and it save to table 5 and it being
under the correct data point in 4? Does this make sense?

Ryan

 

[Marshall Barton]

I have a 6 tables all of which are sub tables of a bigger table, i.e.
1 goes into 2, 2 into 3, 3 into 4, etc down to 6, basically it's a
heirarchy of tables. Now I created a form where I can see all these
tables which I did through list boxes; when I click the first table
list box the selection that I picked in that listbox (table 1) brings
up the selections in list box 2 that pertain to table 1, and that goes
into listboxs for the rest of the tables. Now how can I can input new
data into table 5, in the form, and it save to table 5 and it being
under the correct data point in 4?

I think it makes sense, but using list boxes seems a little
clumsy to me. I suppose you could add a command button near
each list box to open a form where you can add a new record.
If you do this, be sure to open the related form in dialog
mode and follow the OpenForm line with a Requery of the
related list box.

Instead of using list boxes, I would use continuous (or
datasheet) subforms where adding a new record is a normal
operation.

 

[ryan.fitzpatrick3]

Can you help me alittle more, explain what I should do? Please,
thanks.

 

[Marshall Barton]

Sure.

First create a form for each table.

Which design do you want to pursue?

If you want to use list boxes, create the command buttons
using the button wizard to create the basic code to open the
table's form. See VBA Help on the OpenForm method for
details about the DataMode and WindowMode arguments.

If you want to use continuous (or datasheet) subforms
instead of list boxes, then set each form's DefaultView
property to Continuous (or Dataheet) and then drag and drop
them onto the main form in place of the list boxes. Make
the subforms dependent on the previous subform using the
same approach you used for the list boxes.

 

[ryan.fitzpatrick3]

OK sweet, I like the continuous list better. I'll try that. thanks a
lot.

Sure.  

First create a form for each table.

Which design do you want to pursue?

If you want to use list boxes, create the command buttons
using the button wizard to create the basic code to open the
table's form.  See VBA Help on the OpenForm method for
details about the DataMode and WindowMode arguments.

If you want to use continuous (or datasheet) subforms
instead of list boxes, then set each form's DefaultView
property to Continuous (or Dataheet) and then drag and drop
them onto the main form in place of the list boxes.  Make
the subforms dependent on the previous subform using the
same approach you used for the list boxes.
--
Marsh
MVP [MS Access]

Can you help me alittle more, explain what I should do? Please,
thanks.

- Show quoted text -

 

[ryan.fitzpatrick3]

I have all them set as continuous subforms for just the first 3
tables. Now, before when I had a listbox I could click an item and the
following listbox filtered information for that selection would
appear, how do I do this with continuous subform?

Sure.  

First create a form for each table.

Which design do you want to pursue?

If you want to use list boxes, create the command buttons
using the button wizard to create the basic code to open the
table's form.  See VBA Help on the OpenForm method for
details about the DataMode and WindowMode arguments.

If you want to use continuous (or datasheet) subforms
instead of list boxes, then set each form's DefaultView
property to Continuous (or Dataheet) and then drag and drop
them onto the main form in place of the list boxes.  Make
the subforms dependent on the previous subform using the
same approach you used for the list boxes.
--
Marsh
MVP [MS Access]

Can you help me alittle more, explain what I should do? Please,
thanks.

- Show quoted text -

 

[Marshall Barton]

I have all them set as continuous subforms for just the first 3
tables. Now, before when I had a listbox I could click an item and the
following listbox filtered information for that selection would
appear, how do I do this with continuous subform?

The general idea is the same. If you used code to set the
list box's RowSource, then do the same to set the subform's
RecordSource. If you a query parameter, then use the same
criteria in the subform's RecordSource query.

If you have trouble with that, post a Copy/Paste of the code
or query's SQL that you used for the corresponding list box.

 

[ryan.fitzpatrick3]

What I did is on the main form I made a subform with table 1, in table
1 I put table 2, then 3 in 2, 4 in 3, etc etc. It works, it's not
aesthetically pleasing but it gets the job done. Here is SQL of the
listbox from 3 to 2, the rest of the listbox is linked the same way to
the previous one. In actuality the first 2 selections are comboboxes,
because of the small records contained, I go to listbox on tables 3
through 6 because they have a lot more records. Here's table 3 linking
to combobox table 2.

SELECT DISTINCT [3tblGroup].GroupID, [3tblGroup].GroupName,
[3tblGroup].CategoryID FROM 3tblGroup WHERE
((([3tblGroup].CategoryID)=Forms!frmItemMaster!Category));

 

[Marshall Barton]

What I did is on the main form I made a subform with table 1, in table
1 I put table 2, then 3 in 2, 4 in 3, etc etc. It works, it's not
aesthetically pleasing but it gets the job done. Here is SQL of the
listbox from 3 to 2, the rest of the listbox is linked the same way to
the previous one. In actuality the first 2 selections are comboboxes,
because of the small records contained, I go to listbox on tables 3
through 6 because they have a lot more records. Here's table 3 linking
to combobox table 2.

SELECT DISTINCT [3tblGroup].GroupID, [3tblGroup].GroupName,
[3tblGroup].CategoryID FROM 3tblGroup WHERE
((([3tblGroup].CategoryID)=Forms!frmItemMaster!Category));

That should work as subform3's RecordSource.

For subform4, the WHERE clause would be like:

[3tblGroup].CategoryID=Forms!frmItemMaster.subform3.Form!Category