Open 'other' form, if no selected form is not completed
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Open 'other' form, if no selected form is not completed
I have been tasked with putting together a DB that has a number of 'challenges' for me, this being the first.
I will try to be clear, please bear with me if it isn't quite :)
The concept is that when a specific button is clicked another form is opened i.e. frmProb. Simple so far.
But, the user wants a separate form (frmKipling) to open instead, unless all of the fields in frmProb have data.
I hope that makes sense.
Anybody know how I could achieve this?
TIA
EnB
I will try to be clear, please bear with me if it isn't quite :)
The concept is that when a specific button is clicked another form is opened i.e. frmProb. Simple so far.
But, the user wants a separate form (frmKipling) to open instead, unless all of the fields in frmProb have data.
I hope that makes sense.
Anybody know how I could achieve this?
TIA
EnB
-
- Administrator
- Posts: 78444
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Open 'other' form, if no selected form is not completed
We'll need more information.
In the first place: will frmProb simply be opened (using DoCmd.OpenForm "frmProb"), or will it be opened to a specific record related to the current record in the form that contains the button?
(I'll have more questions depending on your reply to this one)
In the first place: will frmProb simply be opened (using DoCmd.OpenForm "frmProb"), or will it be opened to a specific record related to the current record in the form that contains the button?
(I'll have more questions depending on your reply to this one)
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Open 'other' form, if no selected form is not completed
Hi Hans,
Both 'frmProb' and 'frmKipling' are based on tables that relate to a 'parent table.
The plan(?) is that it is opened from the form that is based on the 'parent' record.
Both 'frmProb' and 'frmKipling' are based on tables that relate to a 'parent table.
The plan(?) is that it is opened from the form that is based on the 'parent' record.
-
- Administrator
- Posts: 78444
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Open 'other' form, if no selected form is not completed
I'm afraid that doesn't clarify much (to me). What exactly do you mean by "based on tables that relate to a 'parent table"? Please try to be specific.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Open 'other' form, if no selected form is not completed
Sorry for the confusion.
I'll try to clarify.
To answer your initial question: "frmProb" will be opened to a record that is related to the current record (frmMain).
The record in "frmKipling" will also relate to that same record, in "frmMain" (though a separate table, than that in "frmProb").
I hope that helps.
EnB
I'll try to clarify.
To answer your initial question: "frmProb" will be opened to a record that is related to the current record (frmMain).
The record in "frmKipling" will also relate to that same record, in "frmMain" (though a separate table, than that in "frmProb").
I hope that helps.
EnB
-
- Administrator
- Posts: 78444
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Open 'other' form, if no selected form is not completed
Thanks. Here is the On Click event procedure for a command button on the main form.
Change Table4frmProb to the name of the recordset of frmProb, and MainID to the field that identifies the current record on frmMain.
I have assumed that it is a number field. If it is a text field, change
to
Code: Select all
Private Sub cmdOpenForm_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim blnEmpty As Boolean
Dim strWhere As String
Dim strSQL As String
Dim strFormName As String
' Where-condition
strWhere = "[MainID] = " & Me.[MainID]
' Select the record that would be opened in frmProb
strSQL = "SELECT * FROM [Table4frmProb] WHERE " & strWhere
Set dbs = CurrentDb
' Open the record
Set rst = dbs.OpenRecordset(strSQL)
' Loop through the fields
For Each fld In rst.Fields
' If a field is blank
If IsNull(fld.Value) Then
' Raise a flag
blnEmpty = True
' And get out of the loop
Exit For
End If
Next fld
' We're done with the recordset
rst.Close
Set rst = Nothing
Set dbs = Nothing
' Was any of the fields blank?
If blnEmpty Then
' If yes, use frmKipling
strFormName = "frmKipling"
Else
' Else use frmProb
strFormName = "frmProb"
End If
' Open the form
DoCmd.OpenForm FormName:=strFormName, WhereCondition:=strWhere
End Sub
I have assumed that it is a number field. If it is a text field, change
Code: Select all
strWhere = "[MainID] = " & Me.[MainID]
Code: Select all
strWhere = "[MainID] = " & Chr(34) & Me.[MainID] & Chr(34)
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Open 'other' form, if no selected form is not completed
Hi Hans,
Thank you for the code. That is way over my head; that's another one I owe you.
Got a small (I hope)issue though. If there is not a related record yet, the code hangs at:
Here it is with the appropriate tables and field names:
Private Sub Command6_Click()
Thank you for the code. That is way over my head; that's another one I owe you.
Got a small (I hope)issue though. If there is not a related record yet, the code hangs at:
Code: Select all
strSQL = "SELECT * FROM [tblProb] WHERE " & strWhere
Private Sub Command6_Click()
Code: Select all
'Private Sub cmdOpenForm_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim blnEmpty As Boolean
Dim strWhere As String
Dim strSQL As String
Dim strFormName As String
' Where-condition
strWhere = "[ID] = " & Chr(34) & Me.[ID] & Chr(34)
' Select the record that would be opened in frmProb
strSQL = "SELECT * FROM [tblProb] WHERE " & strWhere
Set dbs = CurrentDb
' Open the record
Set rst = dbs.OpenRecordset(strSQL)
' Loop through the fields
For Each fld In rst.Fields
' If a field is blank
If IsNull(fld.Value) Then
' Raise a flag
blnEmpty = True
' And get out of the loop
Exit For
End If
Next fld
' We're done with the recordset
rst.Close
Set rst = Nothing
Set dbs = Nothing
' Was any of the fields blank?
If blnEmpty Then
' If yes, use frmKipling
strFormName = "frmKipling"
Else
' Else use frmProb
strFormName = "frmProb"
End If
' Open the form
DoCmd.OpenForm FormName:=strFormName, WhereCondition:=strWhere
End Sub
-
- Administrator
- Posts: 78444
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Open 'other' form, if no selected form is not completed
Add the following code at the beginning, just below the declarations:
Code: Select all
If IsNull(Me.[ID]) Then
MsgBox "Create a record first, then try again.", vbInformation
Exit Sub
End If
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Open 'other' form, if no selected form is not completed
Ah, my (big) mistake: the spec is that the frmKipling must be completed, before anything can go into frmProb.
Sorry, only just realised I described it all wrong
Sorry, only just realised I described it all wrong
-
- Administrator
- Posts: 78444
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Open 'other' form, if no selected form is not completed
That's a matter of switching a few things around. Substitute the correct name of the table for frmKipling - I've used tblKipling.
Code: Select all
Private Sub Command6_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim blnEmpty As Boolean
Dim strWhere As String
Dim strSQL As String
Dim strFormName As String
' There must be an ID before we can continue
If IsNull(Me.[ID]) Then
MsgBox "Create a record first, then try again.", vbInformation
Exit Sub
End If
' Where-condition
strWhere = "[ID] = " & Chr(34) & Me.[ID] & Chr(34)
' Select the record that would be opened in frmKipling
strSQL = "SELECT * FROM [tblKipling] WHERE " & strWhere
Set dbs = CurrentDb
' Open the record
Set rst = dbs.OpenRecordset(strSQL)
' Loop through the fields
For Each fld In rst.Fields
' If a field is blank
If IsNull(fld.Value) Then
' Raise a flag
blnEmpty = True
' And get out of the loop
Exit For
End If
Next fld
' We're done with the recordset
rst.Close
Set rst = Nothing
Set dbs = Nothing
' Was any of the fields blank?
If blnEmpty Then
' If yes, use frmKipling
strFormName = "frmKipling"
Else
' Else use frmProb
strFormName = "frmProb"
End If
' Open the form
DoCmd.OpenForm FormName:=strFormName, WhereCondition:=strWhere
End Sub
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Open 'other' form, if no selected form is not completed
Hmm, still hanging on:
Error =
Runtime error '3061':
Too few parameters. Expected 1
Also hovering over this line shows:
strSQL = "SELECT * FROM [tblKipling] WHERE [ID] = "123""
Code: Select all
Set rst = dbs.OpenRecordset(strSQL)
Runtime error '3061':
Too few parameters. Expected 1
Also hovering over this line shows:
strSQL = "SELECT * FROM [tblKipling] WHERE [ID] = "123""
-
- Administrator
- Posts: 78444
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Open 'other' form, if no selected form is not completed
Are you sure that tblKipling is the correct name of the table?
And that ID is the name of the relevant field in this table?
And that it is a text field?
And that ID is the name of the relevant field in this table?
And that it is a text field?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Open 'other' form, if no selected form is not completed
Sorry for the delay in responding - no time.
Will get back ASAP
Will get back ASAP