Newbie - Open Form and Subform Question

Question

I am still trying to fathom certain elements of a drawing control database.

The feature I am looking at is straight forward in that there is a drawing table and a contracts table with a one to many (one drawing can be issued for many contracts.

Within the database is a search form which via a fairly complex select query returns a list of drawing information based on fields from both the drawing table and contracts table.

The bit that I don't get is that there is a Double Click event on the results of this query.  Double clicking a single entry in the results list opens a form with subform which display information from both the drawing and contract tables.  But the Openform command that calls this refers to the drawing number in the main form only.

The syntax of the command is as follows: DoCmd.OpenForm "frmCrownDwg", , , "[cd-no]='" & Me.lstResult.Column(0) & "'", , acDialog

So the event only seems to control the record in the main form that is opened and not the sub-form entry.  At least 9 times out of 10, the frmCrownDwg opens at the correct sub-form record but it is not totally reliable.  Is there any way I can control the record that the sub-form shows as well as the main form by including reference to another column in the Me.lstResult.Column(?).

thanks

Phil

Answer 1

Hi Phil. I'm not sure what you're asking but you can certainly include any criteria you want in the Where Condition argument. For example: "FieldName=" & MainFormValue & " OR FieldName=" & SubformValue

The Me. keyword refers to the object running the code. So, if the code is on the main form, then Me.lstResult refers to the main form.

Hope that helps...

Answer 2

Thanks DB Guy

To try and clarify my ramblings...

The code is issuing an instruction to open the main form and show records based on a field [CD-No] within that main form.  The sub-form seems to be left to its own devices.

What I wasn't sure about was whether the open form command could include fields that are covered by the sub-form even though the open instruction is only being applied to the main form.  From your example above it appears that this is OK (FieldName=" & SubformValue).

The Me.lstResult code is behind a double click event on a search criteria form.  Does that make a difference as to whether the sub-form field can be included in the openform statement?

Phil

Answer 3

Hi Phil. When you create a form and subform, it is usually to represent two entities with a one-to-many relationship. This is done by setting the Master and Child Link Fields on the form. So, when the main form opens filtered to a specific [CD-No], the subform should only display records related to that record on the main form - if the links were properly established. Can you confirm the links were properly established? And if so, what is the problem with the subform when you open the main form to a specific record? Is it not showing the correct related records?

Answer 4

Hi DB Guy

Yes the forms are linked with master and child fields set to CD-No in both tables.

When you access the records directly in the main input form and subform, I am pretty sure that the subform records show contract entries correctly related to the main form record (from the contract table).

The issue is that when you use the double click event on the search form to open a specific record, it goes to the correct drawing record but does not always load the correct contract entry in the sub form.  The search form presents data from both the drawing table and contracts table in one row.  When the user double clicks a single row of the search form, the openform instruction only includes the drawing Number CD-No and doesn't seem to specify anywhere which contract record should be displayed.  For some reason the form and subform opened by  this action do seem to display the drawing-contract entry "double Clicked" most of the time (not sure how) but it is not absolutely reliable which upsets our admin staff.

Phil

Answer 5

Hi Phil. It's hard to picture what you're describing without seeing what you're looking at. The search form is separate from the main form/subform. If, for example, you had a CD-No in the main form with five related records in the subform, are you saying you only want to see one of those records in the subform matching the same child record from the search form? If so, you will have to use a tandem method besides the WhereCondition argument. For example, you can also use the OpenArgs argument. The WhereCondition argument will only be applied to the main form. So, to also filter the subform, you need to use another method. Hope that makes sense...

Answer 6

Hi DB Guy

Yes your understanding is spot on. Yes search form is separate. Yes the search should allow selection of main record CD-No and one of (perhaps) 5 related records.  Finally the main record and individual related record should then be displayed in the main and subform opened at the double click of a record in the search form.

I have looked at the OpenArgs function but, if I understand the description correctly, this would open the main and subforms as read only.  This is a problem cos the admin use this method to navigate to a main record and related record for editing.

Can you give me pointers to another approach please?

Many thanks for the input.

Phil

Answer 7

I have looked at the OpenArgs function but, if I understand the description correctly, this would open the main and subforms as read only.  This is a problem cos the admin use this method to navigate to a main record and related record for editing.

Hi Phil,

Not sure where you read that because it's not true. OpenArgs is just a way to "pass" information to the opening form. What happens with it is up to you when the form opens. You can ignore the information or choose to act on it. It's just information - not instructions.

To use it, here's a possible approach:

Let's assume the CD-No is in column 1 of the listbox and the ChildID is in column 3 of the listbox. You can try the following command:

DoCmd.OpenForm "frmCrwnDwg", , , "[CD-No]='" & Me.lstResults.Column(0) & "'", , , Me.lstResults.Column(2)

Then, in the Open event of the main form, you can try this:

If Not IsNull(Me.OpenArgs) Then

 Me.SubformName.Form.Filter = "IDFieldName=" & Me.OpenArgs

 Me.SubformName.Form.FilterOn = True

End If

(untested)

Hope that helps...

Answer 8

Hi DB Guy

I think I misinterpreted the opening paragraph here https://msdn.microsoft.com/en-us/library/office/ff836583.aspx

Reading your above method, I realised that I hadn't looked at the open-form events on either of my forms.  This would have saved some time - sorry!

On the open event of the sub form I found code which I believe is controlling the record that is opened. This is:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo he
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[cdc-sono]='" & Forms!frmAccountDwg.List120.Column(2) & "'"
rs.FindFirst "[cdc-sono]='" & Forms!frmSearchCrownDrawing.lstResult.Column(6) & "' and [cdc-revision]='" & Forms!frmSearchCrownDrawing.lstResult.Column(4) & "' and [cdc-date]=#" & Forms!frmSearchCrownDrawing.lstResult.Column(7) & "#"
Me.Bookmark = rs.Bookmark
rs.Close
Set rs = Nothing
he:
Resume Next

End Sub

I haven't used Recordsets, Bookmarks etc. but the lines rs.Findfirst appear to be controlling the subform record displayed.  The line that includes the text frmSearchCrownDrawing is the one that I have focused on.

Originally, this line looked at [cdc-sono] and [cdc-revision] only and opened the subform based on the values of these returned in the search result query lstResult.  The problem was that this did not uniquely identify records - hence the anomalies we have experienced.

Soooo what I tried to do was add the date of the record entry as additional criteria for the Findfirst operation.  You will see that I have tagged this on to the search parameters above, but for some reason this is not working.

Can you tell me if the syntax is correct please?  Googling suggests that this field should be defined by # rather than ' or " but I'm not convinced I've got this right.  If I had cocked this up big style I would expect a VBA error but I'm not seeing this.

I have checked the column reference I am referring to (7) a number of times starting with (0) as the left hand column and this seems OK.  The field is defined in the table as type Date/Time.

Thanks for your continued support

Phil

Answer 9

Hi Phil. Since you are dealing with a date value, Regional Settings matter. Can you post what the date value looks like from the listbox column? It has to be in U.S. or ISO format.

Answer 10

Hi DB Guy

I take it from the list box column you mean the search results?

Anyway, this is for a UK company and regional PC/laptop settings use UK date format.

The dates are entered into the Add/Edit contract sub-form in dd/mm/yyyy format.  Hope this is the answer you were looking for.

Thanks DBG

Phil

Answer 11

Hi Phil. Yes, it answers my question and explains the problem. As I was saying, the code expect US or ISO formatted dates. So, you'll need to convert the listbox date column value to this format. For example, you could try the following:

...[cdc-date]=#" & Format(CDate(Forms!frmSearchCrownDrawing.lstResult.Column(7)),"yyyy-mm-dd") & "#"

Hope that helps...

Answer 12

Thanks DBG

It may be this will have to wait till next week now but I'll be sure and report back

Phil

Answer 13

Hi Phil. Sounds good. Have a good weekend.

Answer 14

Hi DB Guy

I have just managed to test the code you sent for including the date as part of my search string.

It took a few attempts as I am still struggling with the number of quotations etc., but it now seems to be returning the correct individual records.

Many thanks for your patient help with this it is much appreciated.

Phil

Answer 15

Hi Phil. Glad to hear you got it to work. Good luck with your project.