Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55

    Unanswered: Switching between forms

    Hi, (I am not a programer(hardwarde guy) but I have some ability.. please be gentle... ;-)

    I have two forms. "frmAllFields" and "frmUpdates". I have a command button on each form that allow me to switch between these forms.

    The "frmUpdates" displays all records, with limited fileds in a horizontal. This allows me to scroll up/and down, and edit these limited fields. In the far left colume is an arrow, that indicates which record I am editing.

    ..the above all works..

    My problem is, when I am in the "frmUpdates" and press the command to go to the "frmAllFields", I go to record #1 and not to the record I am editing.

    Here is the code I have for the button..

    Private Sub Command109_Click()
    DoCmd.OpenForm "frmAllFields"
    End Sub

    sorry this is so basic
    Thanks in advance
    Floyd

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    There are a couple of ways to skin the cat, as usual. In any case, you'll have to use an OpenArgs to be transferred to the the other form. Not a problem. I like the .Bookmark property in this case.

    Before the
    DoCmd.OpenForm "frmAllFields"
    statement, do the following:

    Code:
    Dim BMark
    
    BMark = Me.Bookmark
    DoCmd.OpenForm "frmAllFields", , , , , , BMark
    In frmAllFields, add the following in the Open event:

    Code:
    Me.Bookmark = Me.OpenArgs
    That will set the record in frmAllFields to the same record as in frmUpdaets.

    HTH,
    Sam

  3. #3
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Thanks for the help

    I know Im lame, but you say:

    In frmAllFields, add the following in the Open event:

    Code:
    Me.Bookmark = Me.OpenArgs

    That will set the record in frmAllFields to the same record as in frmUpdaets.

    Im not a programmer.. Im not sure were the 'Open event' is in my frmAllFields...

    sorry if it is obvious for you
    Floyd

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Open frmAllFields in design view. The form's property sheet should be visible. If it's not, right click anywhere on the form and select "Properties" to open it. Click in the upper left hand black square. That's the form event page itself. (You may have to experiment finding it!) In the property sheet, scroll down to where it says "On Open" and right click. In the resulting dialog box, select "Code" to open the VBA editor. In a blank line, between the line Private sub..... and End Sub, you can enter the one line of code I gave you.

    Sam

  5. #5
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Hi Sam, Still having Problems

    in "frmUpdates" Here are the routines: The new mods are in italics

    Private Sub Command109_Click() 'this is "GoTo_All_Fields_Form" Button
    Dim BMark

    BMark = Me.Bookmark
    DoCmd.OpenForm "frmAllFields", , , , , , BMark

    DoCmd.OpenForm "frmAllFields"
    End Sub


    In "frmAllFields: Here are the Form_Open and Form_Load routines:

    Private Sub Form_Open(Cancel As Integer) 'this forces Maximize on open
    Me.Visible = False 'along with the form_load() below
    DoCmd.Maximize
    Me.Bookmark = Me.OpenArgs
    End Sub

    Private Sub Form_Load() 'this goes with above sub
    Me.Visible = True
    End Sub



    When running the above i receive this window
    Run-Time error '3159
    Not a valid bookmark.

    I'm sure it is basic in that I probably have not placed your chunks of code in a proper sub
    Thx, Floyd

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Private Sub Command109_Click() 'this is "GoTo_All_Fields_Form" Button
    Dim BMark

    BMark = Me.Bookmark
    DoCmd.OpenForm "frmAllFields", , , , , , BMark
    DoCmd.OpenForm "frmAllFields"
    End Sub

    Private Sub Form_Open(Cancel As Integer) 'this forces Maximize on open
    Me.Visible = False 'along with the form_load() below
    DoCmd.Maximize
    Me.Bookmark = Me.OpenArgs
    End Sub

    Private Sub Form_Load() 'this goes with above sub
    Me.Visible = True
    End Sub
    Floyd,

    You need to tweak the above subs. One at a time.

    Code:
    Private Sub Command109_Click() 'this is "GoTo_All_Fields_Form" Button
    Dim BMark
    
    BMark = Me.Bookmark
    DoCmd.OpenForm "frmAllFields", , , , , , BMark 
    DoCmd.OpenForm "frmAllFields"
    End Sub
    Remove bolded line. It's redundant.

    Code:
    Private Sub Form_Open(Cancel As Integer) 'this forces Maximize on open
    Me.Bookmark = Me.OpenArgs
    Me.Visible = False 'along with the form_load() below
    DoCmd.Maximize
    End Sub
    
    Private Sub Form_Load() 'this goes with above sub
    Me.Visible = True
    End Sub
    I don't understand the bolded line. Are you hiding the form? If you're not doing that, you need to delete this line; it contradicts the line in the sub below it. Also, make the OpenArgs line the first line in the sub, as show above.

    More to the point: you don't need the Load() sub at all; upon opening, the form defaults to visible without any prompting in VBA. If I were you, I'd delete the entire sub, as well as the bolded line I addressed above. Of course, if you have a valid reason to hide the form, that's a horse of a different color. However, I'd still delete the Load() event code.

    As for the run-time error, I need to ask you the following: what is the record source for the two forms? To get the answer to that, you need to look at the property sheet with each form open in design mode, and you click on the form property square. The top line in the property sheet tells you the record source. If the two record sources are not identical, you'll get that error message, because VBA won't be able to locate the correct record; it simply doesn't exist.

    There may be a different problem, but first things first.

    Sam

  7. #7
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Hi again Paul
    I have made all your suggestions... still the RunTime problem

    When I try to to open the frmAllFields I get the error box
    Run-Time error 3159
    Not a valid Bookmark

    I press the debug button and it takes me to the to the frmAllForms to the line... Me.Bookmark = Me.OpenArgs
    is highlighted in yellow..

    This happens where I try to open the frmAllField from the button or directly with MSAccess

    Any further thoughts
    Thanks again
    Floyd

  8. #8
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Sam
    Also I forget to tell you , I did check and both forms use the same source ... "tblPropertyLists"
    Floyd

  9. #9
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi all

    I am sure you cannot use a bookmark from one recordset in another (or even in the same one after a requery).

    My normal solution to these issues is the use of PKs like this

    Code:
            DoCmd.OpenForm frmName
            
            Form_frmName.Recordset.FindFirst "ID = " & [ID]
    This assumes the PK is available in both forms from the same table
    In this case you need check if the form is open before opening it(?).

    MTB
    Last edited by MikeTheBike; 07-26-07 at 04:25.

  10. #10
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Hi Mike, thanks for looking at this.
    Please remember, Im not a very good programmer.

    I think your assuming I know allot... ;-)

    I am not sure how to insert this command nor exactly how to implement....

    Please read my very first thread and see if you can simplyify it a bit for me if you dont mine... I really appreciate your patience... I am a quick learner however.

    Here is the routine i have now.
    '--------
    Private Sub Command109_Click() 'GoTo_All_Fields_Form" Button
    DoCmd.OpenForm "frmAllFields"
    End Sub
    '--------
    Do I implement your suggestion into that sub like this?
    '-------
    Private Sub Command109_Click() 'Mikes sugg
    DoCmd.OpenForm frmName

    Form_frmName.Recordset.FindFirst "ID = " & [ID]

  11. #11
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55
    Sorry for the thread below.. I did not finish and I accidentaly posted it..

    Mike, are you suggesting the code to look like this?

    Here is the routine i have now.
    '--------
    Private Sub Command109_Click() 'GoTo_All_Fields_Form" Button
    DoCmd.OpenForm "frmAllFields"
    End Sub
    '--------
    Do I implement your suggestion into that sub like this?
    '-------
    Private Sub Command109_Click() 'Mikes sugg
    DoCmd.OpenForm "frmAllFields"
    Form_frmAllFields.Recordset.FindFirst "ID = " & [ID]
    End Sub
    '------

    I am trying to go to the frmAllFields and open the record that I am working on.
    Hope this is clear, sorry for my simple needs.
    I appreciate all your helps.
    Floyd

  12. #12
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Essentially yes that is what I meant, but
    "ID = " & [ID]
    will need to be the PK field(s) in your Table/RecordSource for BOTH forms. The pK field)s) do not have to be used in a control, just included in the RecordSource.

    It also assums A2k or later.


    MTB

  13. #13
    Join Date
    Aug 2006
    Location
    California, USA
    Posts
    55

    Talking

    Hi Mike
    I really appreciate yours and Sam's help... really

    Your both a bit out of my league... I don't quite understand PK and many other programming acronyms(hardware is my world)

    ... but I think I've come up with another way.

    I have added a Button on the end of every row (record) that will 'GoTo' that record. It does make the form a tad busier, but it is effective and works well.
    It is also a bit more intuitive...

    You guys and this forum are the best ....

    Floyd

Posting Permissions

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