|
Subject:
|
Open one form or the other
|
|
Posted By:
|
mean34dean
|
Post Date:
|
8/20/2008 3:33:04 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.
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
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
8/21/2008 10:30:19 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
|
|
Reply By:
|
mean34dean
|
Reply Date:
|
8/21/2008 10:56:58 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.
|
|
Reply By:
|
mean34dean
|
Reply Date:
|
8/21/2008 11:01:37 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??
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
8/21/2008 12:02:44 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
|
