Solved Form with multiple subforms opening blank (1 Viewer)

Sam Summers

Registered User.
Local time
Today, 09:46
Joined
Sep 17, 2001
Messages
939
Hello again,

I have a form 'WeeklyInspections' that i am opening with a an unbound combo box 'BuildingSelect' as the parameter on the form 'Selectionfrm'

A button opens the form 'WeeklyInspections' using this:

Private Sub WeeklyBtn_Click()
On Error GoTo WeeklyBtn_Click_Err

Dim stDocName As String
Dim stLinkCriteria As String

If IsNull(BuildingSelect) Then
DoCmd.Beep
MsgBox "Please select a Building", vbExclamation, "Selection required"
End
End If

stDocName = "WeeklyInspections"
stLinkCriteria = "[Building]='" & Me.BuildingSelect & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

' DoCmd.Close acForm, "Selectionfrm"

WeeklyBtn_Click_Exit:
Exit Sub

WeeklyBtn_Click_Err:
MsgBox Error$
Resume WeeklyBtn_Click_Exit

End Sub

---------------------------------------------------------------------------------------------------

The form 'WeeklyInspections' has four subforms on it all linked via 'BuildingID' to the main form (WeeklyInspections).

The Record Source for (WeeklyInspections) is from the table tblBuildings.

I can open (WeeklyInspections) on its own which displays everything perfectly by cycling through all the buildings using the navigation buttons but i want to display one building at a time and when i try this with the form 'Selectionfrm' i just get a blank form?

I have tried creating a query and also a parameter query but still just blank.

Data entry - No
Allow Additions - No
Allow Deletions - No
Allow Edits - Yes
Allow Filters - Yes

I'm missing something somewhere?

Many thanks in advance

Sam
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:46
Joined
Sep 21, 2011
Messages
14,050
What would be an example of Me.BuildingSelect ?

Have you walked through the code with F8 ?
 

Sam Summers

Registered User.
Local time
Today, 09:46
Joined
Sep 17, 2001
Messages
939
Hi Gasman

SQL is:

SELECT tblBuildings.BuildingID, tblBuildings.Building
FROM tblBuildings;

tblBuildings tblBuildings

BuildingIDBuilding
1​
Matthewsons 1
2​
Matthewsons 2
3​
Macleans 1
4​
Macleans 2
5​
The Byre
6​
Quarry Cottage
7​
Murdos
8​
The Scullery
9​
Hayloft
10​
Schoolhouse
I haven't tried F8 but will just now
 

Sam Summers

Registered User.
Local time
Today, 09:46
Joined
Sep 17, 2001
Messages
939
I have never tried F8 before and just tried but not sure how to do it?
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:46
Joined
Sep 21, 2011
Messages
14,050
First is the BuildingID the bound field?, which is how I would build it.?

See the link in my signature for Debugging VBA.
 

missinglinq

AWF VIP
Local time
Today, 05:46
Joined
Jun 20, 2003
Messages
6,423
...when i try this with the form 'Selectionfrm' i just get a blank form?
When you say a 'blank form,' do you mean a Form with no data? Or do you mean 'blank' as in you cannot even see the empty Controls?

The latter occurs when two conditions are true:

  1. You have no (matching in this case) Records
  2. New Records cannot be added to the Form, for whatever reason
And I notice that you have Allow Additions - No.

Linq ;0)>
 

shokly

New member
Local time
Today, 02:46
Joined
Jan 13, 2021
Messages
7
Just simplify the procedure of opening the 'WeeklyInspections' as follows:
let the rowsource of 'BuildingSelect' combo on 'WeeklyInspections' form to be a query from tblBuildings that take the combo 'BuildingSelect' on 'Selectionfrm' as criteria , and just open the form without setting stLinkCriteria condition.
 

Sam Summers

Registered User.
Local time
Today, 09:46
Joined
Sep 17, 2001
Messages
939
Ok everyone thank you so much for all your help which got me thinking.

In the end i changed the Allow additions from No to yes which made all the subforms appear.

And then after various ideas i put this into the On Open Event of the WeeklyInspections form and it all works perfectly:

Me.Building.SetFocus
Me.Building.Text = Forms![Selectionfrm]![BuildingSelect].Column(1)
 

Sam Summers

Registered User.
Local time
Today, 09:46
Joined
Sep 17, 2001
Messages
939
It seemed like all was fine but of course it isn't!

Now it is adding the selected building to the table tblBuildings and so duplicating buildings so when i reset the Allow Additions to No I get the error -

Screenshot 2021-01-18 130627.jpg


and it breaks at this:

Screenshot 2.jpg


I will keep dabbling to find the answer?
 

shokly

New member
Local time
Today, 02:46
Joined
Jan 13, 2021
Messages
7
Just simplify the procedure of opening the 'WeeklyInspections' as follows:
let the rowsource of 'BuildingSelect' combo on 'WeeklyInspections' form to be a query from tblBuildings that take the combo 'BuildingSelect' on 'Selectionfrm' as criteria , and just open the form without setting stLinkCriteria condition.
Hav you tryed this approach instead of depending of vb code
 

Sam Summers

Registered User.
Local time
Today, 09:46
Joined
Sep 17, 2001
Messages
939
Hav you tryed this approach instead of depending of vb code
Hi there and thank you.

One of the members on here (Mike60smart) very kindly helped me by using BuildingID and then utilising this code so i am now back on track for the minute and learning all the time thanks to all you guys!:

Private Sub WeeklyBtn_Click()

10 On Error GoTo WeeklyBtn_Click_Error
20 If Me.Dirty Then Me.Dirty = False
Dim rnSQL As String
Dim varBuildingID As Long

30 varBuildingID = Me.BuildingID


40 rnSQL = "INSERT INTO PlumbingWeeklyItems (BuildingID) Values ( " & varBuildingID & ")"
50 Debug.Print rnSQL
60 CurrentDb.Execute rnSQL

70 rnSQL = "INSERT INTO InteriorWeeklyItems (BuildingID) Values ( " & varBuildingID & ")"
80 Debug.Print rnSQL
90 CurrentDb.Execute rnSQL

100 rnSQL = "INSERT INTO ElectricalWeeklyItems (BuildingID) Values ( " & varBuildingID & ")"
110 Debug.Print rnSQL
120 CurrentDb.Execute rnSQL

130 rnSQL = "INSERT INTO ExteriorWeeklyItems (BuildingID) Values ( " & varBuildingID & ")"
140 Debug.Print rnSQL
150 CurrentDb.Execute rnSQL

160 MsgBox "All Tables Updated", vbInformation


DoCmd.OpenForm FormName:="frmWeeklyCheck", _
DataMode:=acFormEdit, _
OpenArgs:=Me.BuildingID
170 DoCmd.OpenForm "frmWeeklyCheck", acNormal, "", "", , acNormal
175 Me.Requery
190 On Error GoTo 0
200 Exit Sub

WeeklyBtn_Click_Error:

210 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure WeeklyBtn_Click, line " & Erl & "."

End Sub
 

Users who are viewing this thread

Top Bottom