continuous subform

[Guest]

I have 3 tables, customer, Products, and sales. the sales has the PK from
the other 2 tables. I have created a form based on customers and want to add
a continuous subform for product sales which will show all available
products. The subform should be based on a query? how doo I show all
products?

 

[Guest]

Are you just using the subform to provide a list of products to choose from?
If so, I think it would be much more efficient to use a list box.

But, if you MUST use a subform - since you want ALL products on there then
you would set the subform's recordsource to S

SELECT * FROM YourProductTableName

and don't set Master/Child Links between the subform and main form.
--
Bob Larson
Access World Forums Super Moderator
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista

 

[Guest]

All products will be sold on all orders, so I want to show all products on
the subform. I am having issues in showing all products in datasheet view.

 

[Guest]

I can get the continuous form to show, but with out the master/child how am I
suppose to enter data? I need a relationship between customers and product
sales.

 

[Guest]

If, as you had said, ALL Products will be on ALL ORDERS then why do a
selection anyway. Wouldn't that make every order exactly the same? (which
really doesn't make sense)

Now, if you mean all products should be AVAILABLE for each order then use a
combo box or list box to select the product that the person is ordering in
the Subform.
--
Bob Larson
Access World Forums Super Moderator
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista

 

[Guest]

For a continuous subform, you can do like I did and put a combo box with your
products on your main form and a button to add to the order. Then, I set a
SQL statement to insert to the table and then a requery on the subform to
display the added records.
--
Bob Larson
Access World Forums Super Moderator
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista

 

[Guest]

Ok, all products are available on all orders, not necessarily sold on all
orders. I am just trying to simplify the look of the form, as I will not be
the one entering the data.
I can get the look I want when I base the form on a select query on
products and sales, however when I make this the subform of the customer
table the view changes to just one record.

 

[Guest]

If a combo box is used can you restict it so the same item cannot be added to
the order twice?

 

[Guest]

Yes, you could use a DCount in the event that adds the product and check to
see if one is on the order and to not let it be added if it is.
--
Bob Larson
Access World Forums Super Moderator
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista

 

[Guest]

Could I use a Flex grid to show all products in my subform?

 

[Peter Hibbs]

Dan,

Yes, you probably could although it doesn't sound like the sort of
situation that would need a Flex Grid. Anyway, you can see some
examples of Flex Grids at :-

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='Flex Grid Demo.mdb'

Peter Hibbs.

 

[Guest]

if not a flex grid, than is there a better way to accomplish want. The form
does work with a combo box by selecting a product and then entering number
sold, selecting the next product, etc untill all products have been entered,
but this is tedious. I would prefer to tab through and just enter the Sold
field.

 

[Peter Hibbs]

Dan,

It sounds to me as if Bob Larson's solution is the best option.
Provide a button on your Orders form called 'Add Products' (or
whatever) and have that open another pop up form which shows all the
products in a Multi-Select List box, you would also need to pass a
unique ID code to the pop up form to identify the Order record in the
table.

The user can just select all the products they want to add to this
order and click an 'OK' button to add the selected items to the order
or click a 'Cancel' button to abort. When they click 'OK' you will
need some SQL code to add the selected items to the orders table using
the ID code to identify the correct record in the table, close the
form and then requery the Orders form to show the new products. You
could make the Quantity field default to 1 and the user would then
have to change the Quantity field as necessary (or you could do
something in the pop up form). This method also gets round the problem
of the user entering the same product twice since they can't with this
method.

Sorry I can't be of more help.

Peter Hibbs.

 

[Guest]

ok, I misread bob's solution. this makes sense , but may be beyond my
capabilities in Access. I am still learning, and should probably start with
less ambitious projects.

thanks

 

[Guest]

I have found my solution! with the help of
http://www.techonthenet.com/access/forms/subform_data.php

Private Sub Form_AfterUpdate()


Dim db As Database
Dim LSQL As String
Dim LCntr As Integer

'Establish connection to current database
Set db = CurrentDb()

LCntr = 2

'Create SQL to insert item_numbers 1 to 24
Do Until LCntr > 36

LSQL = "insert into qrysales (EventID, ItemID)"
LSQL = LSQL & " values ("
LSQL = LSQL & "'" & EventID & "', " & LCntr & ")"

'Perform SQL
db.Execute LSQL

'Increment counter variable
LCntr = LCntr + 1
Loop

'Requery subform that new records are listing
frmsales_eventsubform.Requery

End Sub