make sure data is entered before going to new form?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • AccessIdiot
    Contributor
    • Feb 2007
    • 493

    #16
    What do you mean? :-)

    Comment

    • Denburt
      Recognized Expert Top Contributor
      • Mar 2007
      • 1356

      #17
      On your form see if you can enter the information from the first form (the info you want carried to the second form) into the field using the keyboard.

      Comment

      • AccessIdiot
        Contributor
        • Feb 2007
        • 493

        #18
        Well, I'm hoping to make the field invisible, so I need it to be filled in automatically.

        Essentially, I just need to tell Access that when I click on the button to start adding Specimen information that they are all tied to that one Replicate. So no matter how many Specimens I create with the Specimen form, the same Replicate ID is going into the Specimen table.

        Comment

        • Denburt
          Recognized Expert Top Contributor
          • Mar 2007
          • 1356

          #19
          Originally posted by AccessIdiot
          Well, I'm hoping to make the field invisible, so I need it to be filled in automatically.

          Essentially, I just need to tell Access that when I click on the button to start adding Specimen information that they are all tied to that one Replicate. So no matter how many Specimens I create with the Specimen form, the same Replicate ID is going into the Specimen table.
          I understand however for troubleshooting purposes... Make it visible and see if it is updateable (humor me) :) If not then check the underlying recordsource and see if it is updateable in there.

          Comment

          • AccessIdiot
            Contributor
            • Feb 2007
            • 493

            #20
            I'm sorry, I'm a little lost here. If I pass the value using OpenArgs in the DoCmd.OpenForm then it passes successfully to an unbound textbox.
            Code:
            Private Sub Form_Open(Cancel As Integer)               'Pass replicate id when form opens
            If Not IsNull(Me.OpenArgs) Then
                     Me.txt_ReplicateID = Me.OpenArgs
                End If
            End Sub
            But it doesn't get into the database because it is an unbound textbox.

            If I add a textbox that has a control source of tbl_Replicate.R eplicate_ID then I cannot type into it because it is an autonumber field.

            Comment

            • Denburt
              Recognized Expert Top Contributor
              • Mar 2007
              • 1356

              #21
              Sounds like our wires are getting crossed a bit here. Lets backup,


              In other words, I have ReplicateID transferred over to the form but I need to get it into the Specimen table (where Replicate.Repli cateID = Specimen.Replic ateID).
              Replicate.Repli cateID sounds as though it should be the Primary Key set to autonumber.

              Specimen.Replic ateID should be a Forien Key and set as a number field.

              When you open the Specimen form you pass the argument in and the two tables will have a common key.

              Comment

              • AccessIdiot
                Contributor
                • Feb 2007
                • 493

                #22
                Yes, exactly. Except that Specimen.Replic ate_ID is not getting updated and I think it's because when the form is opened the Replicate info hasn't been officially entered into it's own table, so there is no ID to pass. Does that make sense?

                Essentially:
                sbfrm_Replicate has Replicate.Repli cate_ID.
                sbfrm_Replicate has button that opens frm_Specimen
                frm_Specimen has Specimen.Replic ate_ID that should contain the value of Replicate.Repli cate_ID but nothing is showing up.

                My guess is that the data on sbfrm_Replicate hasn't gotten into the table yet so nothing is being passed.

                How do I remedy this?

                Comment

                • Denburt
                  Recognized Expert Top Contributor
                  • Mar 2007
                  • 1356

                  #23
                  The following is really derived from the old DoCmd.DoMenuIte m command but more efficient.

                  Code:
                  DoCmd.RunCommand acCmdSelectRecord
                  DoCmd.RunCommand acCmdSaveRecord
                  :)

                  Comment

                  • AccessIdiot
                    Contributor
                    • Feb 2007
                    • 493

                    #24
                    Can you explain what this does and how it is used? Does it replace current code? Do I still use the OpenArgs?

                    thanks!

                    Comment

                    • AccessIdiot
                      Contributor
                      • Feb 2007
                      • 493

                      #25
                      So I put those two lines of code into the button code that launches the form, ahead of the DoCmd.Open command and am still getting the same error.

                      I checked the table though and the replicate info is going into the replicate table, just not into the specimen table.

                      FYI the error message I am getting is
                      "The Microsoft Jet database engine cannot find a record in the table 'tbl_Replicate' with key matching field(s) 'tbl_Specimen.R eplicate_ID'"

                      Comment

                      • Denburt
                        Recognized Expert Top Contributor
                        • Mar 2007
                        • 1356

                        #26
                        Originally posted by AccessIdiot
                        Yes, exactly. Except that Specimen.Replic ate_ID is not getting updated and I think it's because when the form is opened the Replicate info hasn't been officially entered into it's own table, so there is no ID to pass. Does that make sense?
                        No it doesn't because earlier in the thread you stated that is would work for an unbound control and so it should work for a bound control, unless it isn't updatable such as an autonumber field, however the value would still be stored in the openargs we can test the openargs to see if it has a value using debug (see below code).

                        Originally posted by AccessIdiot
                        Can you explain what this does and how it is used? Does it replace current code? Do I still use the OpenArgs?

                        thanks!
                        Add this to your code before you open the new form (using your open args). This will make sure the reecord in question is saved to the table before you move on to the next form.

                        Try this goto the VBA window press the menu "view" then "Immediate Window" (Ctrl G for shortcut fanatics). Now do you see the window that has a title of immediate? If so then good now your code will look something like this:

                        Code:
                        Private Sub Button1_Click()
                        
                        DoCmd.RunCommand acCmdSelectRecord
                        DoCmd.RunCommand acCmdSaveRecord
                        
                        'The following line will produce any result in the immediate window for your _ review after you test the form come back here and look in the immediate _ window for your value.
                        
                        Debug.print  Me!Replicate_ID
                        
                        Docmd.OpenForm,,,,,,Me!Replicate_ID
                        
                        End sub
                        How are we doing now?

                        The RunCommand essentially replaces the code in the first page of this post (I mentioned me being tired should have said exhausted) my appologies it is effective but nasty.

                        Code to remove:
                        Code:
                        if me.dirty then
                        MyBookMark = Me.Bookmark
                        Me.Requery
                        Me.Bookmark = MyBookMark
                        end if
                        Proper method:
                        Code:
                        DoCmd.RunCommand acCmdSelectRecord
                        DoCmd.RunCommand acCmdSaveRecord

                        Comment

                        • AccessIdiot
                          Contributor
                          • Feb 2007
                          • 493

                          #27
                          Originally posted by Denburt
                          No it doesn't because earlier in the thread you stated that is would work for an unbound control and so it should work for a bound control, unless it isn't updatable such as an autonumber field, however the value would still be stored in the openargs we can test the openargs to see if it has a value using debug (see below code).
                          Okay in the immediate window I see the number. I know it is getting passed because it does show up in the unbound textbox when I assign me.txt_Replicat eID = Me.OpenArgs.

                          But if I have a textbox that is bound to tbl_Replicate.R eplicate_ID or tbl_Specimen.Re plicate_ID on my form I do not see the number. tbl_Replicate.R eplicate_ID is an autonumber PK. tbl_Specimen.Re plicate_ID is a number field FK and they are tied in a one to many where one replicate can have many specimens but one specimen belongs to only one replicate.

                          Do I have the relationships wrong?

                          Comment

                          • Denburt
                            Recognized Expert Top Contributor
                            • Mar 2007
                            • 1356

                            #28
                            Backing up a couple of posts lets try it manually, In your tbl_Specimen Form can you enter records including the tbl_Specimen.Re plicate_ID field? If it is a number field you should be able to manually type in the number from the previous form. If you can then everything should work, if not then we need to look at your recordsource for that form.

                            Comment

                            • AccessIdiot
                              Contributor
                              • Feb 2007
                              • 493

                              #29
                              Originally posted by Denburt
                              Backing up a couple of posts lets try it manually, In your tbl_Specimen Form can you enter records including the tbl_Specimen.Re plicate_ID field? If it is a number field you should be able to manually type in the number from the previous form. If you can then everything should work, if not then we need to look at your recordsource for that form.

                              Yes I can manually enter in the value. It barks if I enter in the wrong value. It only goes into the db if I manually enter the value. The control source is tbl_Specimen.Re plicate_ID

                              Comment

                              • Denburt
                                Recognized Expert Top Contributor
                                • Mar 2007
                                • 1356

                                #30
                                Originally posted by AccessIdiot
                                The control source is tbl_Specimen.Re plicate_ID
                                And is the control name:
                                Replicate_ID
                                If so then

                                Me!Replicate_ID = me.openargs

                                Should work...

                                Comment

                                Working...