Open one form or the other

mean34dean · August 20th, 2008, 03:33 PM

I'm still pretty new to VBA so, please bear with me.
I know what I want this button to do but I am not sure how the code should look to make it work. Here is a very lame attempt that does not work. Please help.

Code:

Private Sub Command9_Click()
Dim sLink As String

If IsNull(Me.RcID) Then
        MsgBox "Please enter a record number."
    Else
DoCmd.OpenForm "frmRate", acNormal, , Me.RcID=[RateID] And  [DualLine]='1'
    Else
DoCmd.OpenForm "frmRate2", acNormal, , Me.RcID = [RateID] And [DualLine]= '2'


End If

End Sub

SerranoG · August 21st, 2008, 10:30 AM

Assuming that record number is something your office created and is NOT the autonumber that Access creates (which users should never see), then you'd want something like

If IsNull(Me.RcID) Then
MsgBox "Please enter a record number.", vbExclamation, "No Record Number!"
Else
DoCmd.OpenForm "frmRate", acNormal, , "[RateID] = " & Me.RcID & " And [DualLine] = '" & Me.DualLine & "'"
End If

Assuming DualLine is text as you had it.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

mean34dean · August 21st, 2008, 10:56 AM

Rc.ID is an entry on the first form.
[DualLine] is a field on the form that is being opened as is [RateID]

All are number fields. And yes, [RateID] is an autonumber field.

mean34dean · August 21st, 2008, 11:01 AM

I need to use the [DualLine] field to determine which form to open somehow.
[DualLine]=1 then open frmRate
[DualLine]=2 then open frmrate2

It is stored in the table where the data resides and the queries that feed the respective forms both pull it.

I may be going about this totally wrong??

SerranoG · August 21st, 2008, 12:02 PM

Well for starters, users should never use or see autonumber fields. Those are there solely for the purpose for Access to (in the background) match primary keys with secondary keys from one table to another. If you wish to utilize an identifier people use and see, create one that makes sense and is not an autonumber.

For example, we have off-line tracking numbers for permits temporarily taken off-line: OLT-2008-0001. The autonumber (a six-digit long integer) used by Access to relate one table to another is hidden. We only refer to the OLT number when talking to people.

That said, keeping RcID as the primary key for the moment, and assuming that DualLine is already entered on your form, then

Dim strForm as String

If IsNull(Me.RcID) Then
    MsgBox "Please enter a record number.", vbExclamation, "No Record Number!"
Else
    If Me.DualLine = 1 Then
        strForm = "frmRate"
    Else
        strForm = "frmRate2"
    End If

    DoCmd.OpenForm strForm, acNormal, , "[RateID] = " & Me.RcID & " And [DualLine] = " & Me.DualLine
End If

Or even more compact is...

If IsNull(Me.RcID) Then
    MsgBox "Please enter a record number.", vbExclamation, "No Record Number!"
Else
    DoCmd.OpenForm Replace("frmRate" & Me.DualLine, "1", ""), acNormal, , "[RateID] = " & Me.RcID & " And [DualLine] = " & Me.DualLine
End If

That Replace function takes the form name combined with the dual line number and replaces anything with a "1" in the name with an empty string so that "frmRate1" becomes "frmRate" but "frmRate2" is untouched.

Come to think if it, if you're using an autonumber for RcID, then the IsNull() will never happen because when you create a new record, it always automatically pops in.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division