Page 1 of 2 12 LastLast
Results 1 to 15 of 28

Thread: VBA Help (2000)

  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Help (2000)

    Hi,
    Is there a command or code line that easily closes a form and then opens it again? Here's my problem. I have 2 tables PR & Contract. I have 2 forms based on those tables. The two tables have a One-to-One relationship and the txtPRNo is the primary key. The PR form has a button that when clicked opens the Contract form, pushes the txtPRNo from the PR table to the contract table. All that works great so far.

    Private Sub cmdCreateCoverPage_Click()
    Dim stDocName As String
    stDocName = "frmContractCoverPage"
    DoCmd.OpenForm stDocName, , , , acFormAdd
    Forms!frmContractCoverPage.txtPRNo = Me.txtPRNo
    Forms!frmContractCoverPage.Refresh
    End Sub
    My problem is, the contract form is based on a query that includes the contract table the PR table and a couple others. There are 4 fields that are on the Contract form that come from the PR table. When the Contract form is opened from the PR button is shows the txtPRNo but it will not show those other 4 fields until I close out of the form and then open back up. How can I get those other 4 fields to show it's data without having to close the form and open it back up?
    Thanks,
    Deb

  2. #2
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Help (2000)

    I've tried changing my code to below, but once again I'm sure I'm missing some syntax: txtPRNo is a text field.
    DoCmd.OpenForm stDocName, , , , acFormAdd
    Forms!frmContractCoverPage.txtPRNo = Me.txtPRNo
    DoCmd.Close acForm, stDocName
    stLinkCriteria = "[txtPRNo]=" & Forms!frmPRExistRich.txtPRNo
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Thanks,
    Deb

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Help (2000)

    Deb,

    Did you try Me.Requery on your screen so you refresh the source.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  4. #4
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Help (2000)

    No I didn't, where would I put it.
    I've changed my code to this and it is doing what I want but seems cumbersome, form flickers and everything before it gives the end result. Anything cleaner out there?
    Thanks,
    Deb

  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Help (2000)

    Deb,

    Your problem is that the contract form is set to to add. This will not display any other data.

    What I would do is run a query to populate your contract using the data from your first form and then open your new form with the criteria for the id to be equal to
    forms!yourPRFormName!id

    Private Sub cmdCreateCoverPage_Click()
    dim strSQl as string
    Dim InsSQL as string, SelSQL as string, FrmSQL as string
    Dim stDocName As String

    InsSQl = "INSERT INTO tbl_Contract ( id, Fld1,Fld2,Fld3,Fld4 )
    SelSQL = "SELECT " & ME.id & ", """ & Me.Fld1 & """, """ & Me.Fld2 & """, """ & Me.Fld3 & """, """ & Me.Fld4
    FrmSQL = "FROM tbl_PR;"
    strsql = InsSQL & " " & SelSQL & " " & FrmSQL
    currentdb.execute (strSQL)

    If your fields are numeric the double quotes are not necessary. The reason you see three in places is that they need to part of the string and two quotes become one the outer quotes are the start and stop of a string.

    stDocName = "frmContractCoverPage"
    DoCmd.OpenForm stDocName, , , "txtPRNo = " & Me.txtPRNo, acFormAdd
    End Sub
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  6. #6
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Help (2000)

    I think I understand most of that code. But I think one problem with it is the other 4 fields are in the PR table not the contract table. This is the code I"m using now, which works but seems cumbersome.

    stDocName = "frmContractCoverPage"
    DoCmd.OpenForm stDocName, , , , acFormAdd
    Forms!frmContractCoverPage.txtPRNo = Me.txtPRNo
    DoCmd.Close acForm, stDocName
    stLinkCriteria = "[txtPRNo]='" & Me![txtPRNo] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Thanks,
    Deb

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Help (2000)

    Deb

    Sorry I didn't get back sooner but I lost my connection.

    Your first open is opening the form in Add mode and will not display any rows. This make the form data entry not edit mode

    stDocName = "frmContractCoverPage"
    DoCmd.OpenForm stDocName, , , , acFormAdd <--------<<< Opens it in data entry mode
    Forms!frmContractCoverPage.txtPRNo = Me.txtPRNo
    DoCmd.Close acForm, stDocName
    stLinkCriteria = "[txtPRNo]='" & Me![txtPRNo] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria <---<<< This one opens it in edit mode
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  8. #8
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Help (2000)

    But I need it in add mode to add a new contract. If I add it in edit mode it will not create a brand new contract.
    Deb

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Help (2000)

    All the Add mode does is restrict the visible records to those being added in the current session. Edit mode allows you to add OR edit.
    Charlotte

  10. #10
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Help (2000)

    Yes, I'm aware of that. I want to click on a button and have it open a form so I can add a new record without having to click on anything else. The last code I listed does what I want, what I'm asking if there is a cleaner way to do it.
    Thanks,
    Deb

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: VBA Help (2000)

    Any chance of you sending a zipped stripped down database?

  12. #12
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Help (2000)

    I guess I'm confused as why that would be necessary. I have posted the code that I have and that works. Since I'm not very VBA savvy my question is given the code I have posted, is there a cleaner way of doing that.
    Thanks,
    Deb

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: VBA Help (2000)

    It's amazing what a little testing can achieve.

    But that's up to you.

  14. #14
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Help (2000)

    I appreciate the suggestion and all the help, but I've explained my situation in the previous posts and it's not easy to scale down the database because of it's size and honestly while I agree with your statement, I don't think it's necessary in this particular situation. As I mentioned, the code posted does exactly what I want. It just seems cumbersome. I'm looking to see if there's a cleaner way. Thanks, Deb

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: VBA Help (2000)

    So what you are trying to do is to add a record to the contract table then look at the contract form with those other fields. Isn't the problem the fact that a record has not been added to the contract form at the time the contract form is opened? Or am I missing something?
    What I would do is to use an INSERT INTO to add a record to the contract table prior to opening the contract form, this way you don't need to go into the contracts form in ADD mode, just go into the contract form in EDIT mode as the contract record for the particular txtPRNo will already be there.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •