Dynamic Fields in Access Form

[VBABasix]

I have a table that has a row for each order number. I would like to display in a form the customer and all orders that have been placed and the date of the order. How do I create the fields in the form dynamically?


Thanks

 

[Norie]

Are you sure you need to create fields/controls dynamically for this?

Couldn't you just have a form with the customer which had a subform containing all the orders for the customer?

 

[SydneyGeek]

If you already have a Customers form, create an Orders subform with Customer No, Order No, Order Date, etc. Set the form to display as Datasheet. You can also elect to hide the Customer No field. Close and save the subform.

In Design view in the Customer form, make sure that you have the size estored so it doesn't fill the window then press the {F11} key to display the database container. Click the icon for the subform, drag it onto a blank area of the Customers form, and drop. Now, IMMEDIATELY go to the Properties window of the subform (click the Properties button) and go to the Data tab. In the Link Master (or Parent) and Child fields rows, make sure the Customer No field is entered.

Note: Change field names above to suit your setup.

Save the form and go for a browse. For each customer you will have an ordering history in the subform. This will update dynamically as they place further orders.

Denis

 

[VBABasix]

I was thinking to use hte dynamic fields becuase I do not know which customers have multiple orders and which do not. THe users have to update data on the form and this then generates a report, I will try to sub form to see if it will work for this.

Thanks

 

[VBABasix]

Sub form works great for this. Now next problem is that I have a report and am trying to limit the report based upon the record in the sub form. I am doing this via a command button that runs a query with the sub form field as the limiting criteria. When doing this I am getting an error that is prompting for the value in the sub form. Belos is the code for the command button.
Form_filing is the main form and Frm_Equip is the sub form and ID is what
I want the report to be limited to.

Private Sub Frm_Send_Click()
Dim strSQL As String
On Error GoTo Err_Frm_Send_Click

strSQL = "Update TD_Paperwork SET Submit_Date = Now(), Filed = True WHERE ID = [Forms]![FORM_FILING_NEW]![FRM_EQUIP].[Form]![ID] ;"
'strSQL = "UPDATE TD_Paperwork

DoCmd.SetWarnings False
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.RunSQL strSQL
DoCmd.SendObject acSendReport, "RPT_Filing_1", acFormatRTF, "to", , , "Filing",
DoCmd.SetWarnings True


Exit_Frm_Send_Click:
Exit Sub

Err_Frm_Send_Click:
MsgBox Err.Description
Resume Exit_Frm_Send_Click

End Sub

Thanks in Advance!

 

[SydneyGeek]

Access doesn't like to target subforms that way. You need to get the ID value to the main form, and use that for the filter.
Do the following:

1. In Design view on the form, add an unbound textbox. You can put it anywhere you like, because in the end you will hide it (set its Visible property to No).

2. Set the Control Source for the textbox to be:
=[Insert subform name here].Form![Insert key field name here]

3. Give the textbox a name like txtFilter, and use that textbox as your filter criterion for the report. Before you hide the control, try it out and see that the value changes when you select different records in the subform.

Denis

 

[VBABasix]

I did as you siggested, the problem I am still having is that there are multiple items in the sub form. When I add the sub form ID to the main form the report does not identify that there are multiples. I think that the real problem is in the form design. I am using the same table for the main and sub form. The table contains a listing of all equipment, I was using the sub form to group the equipment by customer so the ID in the main form is the table ID as is the ID in the sub form.

 

[Norie]

Perhaps you should have 2 tables, maybe even 3?

One for customer details, one for equipment details and possibly a third which stores IDs from the other two tables to indicate which customer has which equipment.

 

[VBABasix]

My question woul still be if I have multiple ID in the sub form how will the field that is added to the main form know that there are multiple items in the sub form that should be included?

Thanks!

 

[Norie]

I think you really need to rethink how you have your table(s) set up before you start worrying about forms/subforms.

Can you tell us the current structure you have?