UP DATING A FORM IN ADVANCE 1

[Kevsim]

I am still having a few problems in learning how to handle Access, my query is as folllows-
I have 5 tables, each table is updated from a form (forms 1 to 5), one form for each table. The tables are linked, table 1, linked to table 2 and so on. I update form 1 which updates table 1 for Product. I then manually open form 2, which is for Component, I again select a Product (by combo box), then select or type in the Component, and update the form. I then manually open form 3, select the Product, (by combo box), then select the Component (by combo box), then select or type in the Part, and so on. My question is, how can I update form 1 and on After Update for Form 1, form 2 automatically opens and the product data is displayed, I only have to make a selection from a Combo Box for the Component. When form 2 has been updated, form 3 automatically opens, the product and component data is displayed, I only have to select the part from a combo box or type the part in the box.
Could an Option Button be used to start the process from the first form the new data is inserted in?
The reason for displaying the past data is to confirm no mistakes made on data entry. I would appreciate any advise .
kevsim

 

[LittleSmudge]

So - lets clarify your schema:-

tblProduct
ProductId PrimeKey
ProductInfo


tblComponent
ComponentId PrimeKey
ProductRef ForeignKey linking to tblProduct
ComponentInfo

tblPartType
PartTypeId PrimeKey
ComponentRef ForeignKey linking to tblProduct
PartTypeInfo

Then on frmProduct you have the combo box that allows you to select a product.
You also have a comtrol called txtProductId bound to the ProductId field ( Make this .Visible = No if you don't want the user to see it. )

In the combo box's Afterupdate event put the code:-

Private Sub cboPickProduct_AfterUpdate()

DoCmd.OpenForm "frmComponent", acNormal, ,"ProductRef = " & txtProductId

End Sub

This will open the frmComponent and only show component records that match the Product that is currently on frmProduct.


Repeat the whole thing for updating Component and opening frmPartType using
DoCmd.OpenForm "frmPartType", acNormal, ,"ComponentRef = " & txtComponentId


'ope-that-'elps.

G LS

 

[Kevsim]

LittleSmudge, Thank you for the info, sorry to be a pest, when the form Component opens After Update of the Product form, how does the Component form display records from the Product form. Do I have to add extra controls on the Component form to show the Product form info? At present there is a combo box on the Component form, where I select a Product and a second combo box that only allows selection from what is available from the selection of the first box. What changes would I have to make?
kevsim

 

[LittleSmudge]

Oh, So you want Product data to appear on the component form !

Then you have a few options

1) Forget the multi form approach and go to SubForm, subsubForm, subsubsubForm etc
So you have the Product Info on the main form. Instead of opening a new form for the Component you just make that data appear in a subForm and once you select a Component then a subForm within that appears to display the PartType etc..

2) You have the RecordSource on the second Form ( Component ) as an internally stored query that combines the Product and Component tables so that you can bind the controls directly to any fields that you want.

3) You have unbound Product controls on the Component form that you populate on Form_Open - either through DLookUps in the controls individual ControlSource properties or by putting some code in the Form_Open event

2) Will possibly be the best solution if space allows.
2) Will be fine if you want to practice dynamic queries on Forms
3) Will be fine if you want to practice DLookups or RecordSet manipulation in code.

Pick an option and I'll go into detail on that one.
( If option 3(Recordsets) then what version of Access you're using is important ?)


'ope-that-'elps.

G LS

 

[Kevsim]

LittleSmudge, thanks once more for the info, I believe option 3 would be my best choice, by opening the form with some code in the form open event. Thanks for proposing a solution.
kevsim

 

[Kevsim]

LittleSmudge, I tried your code-
DoCmd.OpenForm "frmComponent", acNormal, ,"ProductRef = " & txtProductId and changed it too -
DoCmd.OpenForm "ComponentInput", acNormal, ,"Product = " & ProductId
I put it in the forms AfterUpdate and the following happened -
My Product form is called ProductInput with bound controls Product and ProductID. My Component form is called ComponentInput with bound controls Product and Component. When I updta the ProductInput form, the ComponentInput form opens and a message box appears saying "Component Input enter paramater value, then product written just above the windoe, could you please advise what I am doing wrong.
kevsim

 

[LittleSmudge]

Okay Kevin

Here is your problem
My Product form is called ProductInput with bound controls Product and ProductID. My Component form is called ComponentInput with bound controls Product and Component.

You really need to get a naming convention sorted out and stick to it.
Recommend:-
Table Names start with tbl
Tables should be named according to the DATA that you are storing in them. NOT according to what you are doing with them at the time you are creating them. ( Otherwise EVERY table name would end in "Input" )
Prime Keys end in Id with root name based on the table name
Foreign Keys end in Ref with root name based on table name


What does your Component TABLE look like ?

tblComponent
ComponentId PrimeKey
ProductRef ForeignKey linking to tblProduct
ComponentInfo

Your Component Form ( frmComponent ) should have ONE bound control = ComponentInfo and - to make life easier later, one other bound control that is not visible = ProductRef.
( The Product info will appear in unbound controls that are populated via the Recordset manipulation as discussed earlier ( Option 3 above )

DoCmd.OpenForm "ComponentInput", acNormal, ,"ProductRef = " & ProductId


'ope-that-'elps.

G LS

 

[Kevsim]

LittleSmudge, Once more thank you for the info. Commenting on Tables, I call them for what they are, Products are T_Products, Components are T_Components etc; For forms, I name them as to how I use them, ProductInput is for inputting products, ComponentInput is for inputting Components, etc: I use forms for searching, preventing others from changing data, for an example, ProductSearch, ComponentSearch, etc: My tables are structured as follows, Table Product, ProductID and Product, Table Component, ComponentID, ProductID, and Component etc: When introducing a new Product, I complete the ProductInput form, I then open the Component update form and re select the product, then select or type in a Component. When the component form is opened, I do not want to select a Product again, I want this information to already populate the form. I tried the code you provided and changed to suit-
Private Sub Form_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "F_CoponentInput"
DoCmd.OpenForm stDocName, acNormal, , "ProductID =" & ProductID,
The Component form opened but no other info, I had a Bound control on form Component “ProductID”, it only showed record zero, I tried to input data and a message box “ You must first select a product, or simular words.
I would appreciate knowing what I am doing wrong.
kevsin

 

[LittleSmudge]

If you insist on using non-standard object naming notation you do make it very defficult for other people to help you - and more importantly to maintain your database at a later date. - Why the insistance on avoiding Hungarian Notation ?

The code in your last post is fine as it stands.
I assume that you are still sticking with the resolution in your 22nd Sept post to use "option 3" so you noww need to add the recordset code into the OnOpen event for the newly opening form.

And as I said on 19th Sept
Pick an option and I'll go into detail on that one.
( If option 3(Recordsets) then what version of Access you're using is important ?)


G LS

 

[Kevsim]

LittleSmudge, thank you for your help. I finally made it work with the below code.
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "F_AssemblyInput"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms![F_AssemblyInput]![Combo22] = Forms![F_productInput]![ProductID]
kevsim