Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 20th, 2008, 03:33 PM
Authorized User
 
Join Date: Mar 2008
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Open one form or the other

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
Reply With Quote
  #2 (permalink)  
Old August 21st, 2008, 10:30 AM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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 With Quote
  #3 (permalink)  
Old August 21st, 2008, 10:56 AM
Authorized User
 
Join Date: Mar 2008
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 With Quote
  #4 (permalink)  
Old August 21st, 2008, 11:01 AM
Authorized User
 
Join Date: Mar 2008
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 With Quote
  #5 (permalink)  
Old August 21st, 2008, 12:02 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Close all MdiChield form from open one form/Button salman .NET Framework 2.0 6 December 10th, 2007 03:21 AM
Close all MdiChield form from open one form salman .NET Framework 1.x 0 November 8th, 2007 12:32 AM
Open Form, run query, open form Grafixx01 Access 7 April 26th, 2007 11:32 AM
Determine if a form is open Danton Access VBA 1 October 7th, 2004 04:59 PM
open a file in a form jobalistic Access 3 September 1st, 2003 10:45 AM



All times are GMT -4. The time now is 12:21 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.