Open 'other' form, if no selected form is not completed

[Egg 'n' Bacon]

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

[HansV]

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)

[Egg 'n' Bacon]

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.

[HansV]

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.

[Egg 'n' Bacon]

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

[HansV]

Thanks. Here is the On Click event procedure for a command button on the main form.

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

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

Code: Select all

    strWhere = "[MainID] = " & Me.[MainID]

to

Code: Select all

    strWhere = "[MainID] = " & Chr(34) & Me.[MainID] & Chr(34)

[Egg 'n' Bacon]

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:

Code: Select all

    strSQL = "SELECT * FROM [tblProb] WHERE " & strWhere

Here it is with the appropriate tables and field names:
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

[HansV]

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

[Egg 'n' Bacon]

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

[HansV]

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

[Egg 'n' Bacon]

Hmm, still hanging on:

Code: Select all

    Set rst = dbs.OpenRecordset(strSQL)

Error =
Runtime error '3061':
Too few parameters. Expected 1

Also hovering over this line shows:
strSQL = "SELECT * FROM [tblKipling] WHERE [ID] = "123""

[HansV]

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?

[Egg 'n' Bacon]

Sorry for the delay in responding - no time.
Will get back ASAP