Creating Separate form for each record in one Table

[sreepal]

I have created a Product Table which consists of more than 20 products, now i
want to create individual form for each record(each product)in Product
table, unlike showing all the products in one form by using next or previous
buttons

Iam much obliged if i can get the answer


Thank You !

 

[John W. Vinson]

I have created a Product Table which consists of more than 20 products, now i
want to create individual form for each record(each product)in Product
table, unlike showing all the products in one form by using next or previous
buttons

Iam much obliged if i can get the answer


Thank You !

You must mean something other than an Access Form. A Form is just a window, a
tool that lets you view and edit data in a table. A Form can be in Datasheet
view (one row per record in the table), Continuous view (one flexible screen
area repeated for each record, it could be of any size), or Single Form view
(one screen of controls per record).

I'm not at all sure which of these - if any - you have in mind, but those are
Access' form options.

 

[sreepal]

Thank You for responding but what my question is when we create a form using
a table, it shows the information of all records in that table by using
first ,next or previous,last buttons,but i want to display each record in
separate window or form, i don't want to press next or previous to view all
records.
Iam sorry if still my question is vague to u.
I hope i have plucked the right time .

Thank You !

 

[ken]

Are you sure you want to do this? It can be done, but with a large
number of records in a table its going to be very unmanageable with
all those windows open. The way to do it is to add separate instances
of form to a collection. For instance, with a table of companies, to
show each company in a separate instance of a frmCompanies form, you'd
firstly put the following in the Declarations area of a module:

Dim frm As Form
Dim colPopups As New Collection

Then add the a function along these lines to the module:

Function StackCompanyForms()

Const conSQL = "SELECT * FROM Companies Order By Company"

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSQL As String
Dim n As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(conSQL)

Do While Not rst.EOF
n = n + 1
Set frm = New Form_frmCompanies
colPopups.Add frm, frm.hwnd & ""
frm.Caption = "Company " & n
frm.Filter = "CompanyID = " & rst.Fields("CompanyID")
frm.FilterOn = True
frm.Visible = True
DoCmd.MoveSize n * 360, n * 360
rst.MoveNext
Loop

End Function

Calling the MoveSize method 'stacks' the forms form top left to bottom
right, but with more than a small number of records you'd soon run out
of screen space, in which case you'd have to amend the code so it
restarts the 'stack' after a certain number.

To close all instances of the form simultaneously put the following
function in the same module:

Function CloseCompanyForms()

Dim n As Long

For n = 1 To colPopups.Count
DoCmd.Close acForm, "frmCompanies"
Next n

End Function

Ken Sheridan
Stafford, England

 

[sreepal]

I am greatful to ur answer and one more thing i would like to ask is how can
i create a combo box that looks up the values of all products in product
table ,for instance when i select a certain product from the combo list it
should display only that particular product information on the form, and i
want the combo box in some other form which is not related to any table
(because i have many products tables like SlimmingProducts_Table ,
NutritionProducts_Table and PainReliefProducts_Table and each table contains
their own product list again)so now i want all those to be dispalyed in some
other external form(like "welcome to productslist_Form" where the user can
get the flexibility to select a particular product information from
particulat product type)where we link them to actual product forms.

Thank you and hoping to get the response.

 

[ken]

Its not a good idea to have separate tables for each product
category. A far better solution is to have a Products table and a
ProductCategories table. In the products table include a foreign key
ProductCategory column which references the primary key of the
ProductCategories table. In the relationship between the two enforce
referential integrity.

You can easily fill the new tables from your existing one by running a
series of pairs of Append queries such as:

INSERT INTO ProductCategories (ProductCategory)
VALUES ("Slimming");

INSET INTO Products (Product, ProductCategory)
SELECT Product, "Slimming" FROM SlimmingProducts_Table;

Repeat for each product category table, changing the above SQL
appropriately for each one. There may of course be other columns in
your existing tables from which you'll need to insert values into the
new single table, but that's merely a case of including them in the
second append query above.

As regards finding a product you can do it all in one form:

In a form bound to the Products table add an unbound combo box
cboProductCategory with a RowSource:

SELECT ProductCategory FROM ProductCategories ORDER BY
ProductCategory;

and an unbound combo box cboProduct with a RowSource

SELECT Product FROM Products WHERE ProductCategory = Form!
cboProductCategory ORDER BY Product;

In cboProductCategory's AfterUpdate event procedure requery cboProduct
with:

Me.cboProduct.Requery

and in cboProduct's AfterUpdate event procedure filter the form with:

Dim ctrl as Control

Set ctrl = Me.ActiveControl

If NotIsNull(ctrl) Then
Me.Filter "Product = """ & ctrl & """"
Me.FilterOn = True
Else
Me.FilterOn = False
End If

Ken Sheridan
Stafford, England