Results 1 to 7 of 7
  1. #1
    mwabbe is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    79

    Form Help with passing records

    Hello,



    I have a form called empInfo and one called empBen they both have pkeys: empid, benid. I am using openArgs method to pass the empid, Fname, and Lname from the empInfo form to the empben form which works and i save it then when i go to open that record again the empinfo form is fine and brings up the record. then i hit the save and open empben button. it does not bring up the record it makes a new one. all fields are empty except the openargs that were passed and it creates a new benid for the record and is not pulling the record already created.

    any help would be appreciated

    here is the code for the empben form onload

    Code:
    Dim strOpenArgs() As String
      If Not IsNull(Me.OpenArgs) Then
        DoCmd.GoToRecord , , acNewRec
        DoCmd.GoToControl "Text44"
        strOpenArgs = Split(Me.OpenArgs, ";")
        Me.EmployeeID = strOpenArgs(0)
        Me.Text44 = strOpenArgs(1)
        Me.Text46 = strOpenArgs(2)
      End If

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    the point is not here.
    you need to figure out why and where to cause a new record added.
    maybe you create a new record whenever you open form empben?

  3. #3
    mwabbe is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    79
    what does form.dirty mean.

  4. #4
    mwabbe is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    79
    this is the code from the btn that opens the next form with the code i already posted on it

    Code:
    If (Form.Dirty) Then
            DoCmd.RunCommand acCmdSaveRecord
            DoCmd.OpenForm "Benefits", acNormal, , , , acDialog, Me.ID & ";" & Me.First_Name & ";" & Me.Last_Name
        Else

  5. #5
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    You really shouldn't need the DoCmd.RunCommand acCmdSaveRecord code if you have an autonumber field in the table (which I'd recommend in the table if you don't have this field type already). This kind of code is used to force a save when an autonumber isn't in the table and you need to 'force' MSAccess to save the record since in older versions of MSAccess, MSAccess didn't always know when to save the record on the form without an autonumber field.

    Typically, what I do when I want to...

    1. Open a form and create a new record:
    Docmd.openform "MyFormName"
    docmd.gotorecord,,acnewrec

    2. Open a form on a certain record (I use the WHERE parameter):
    dim varID as variant
    varID = me!MyIDField
    DoCmd.OpenForm "MyFormName", , , "[IDField] = " & varID & ""
    (and IDField is the autonumber field value)

    If you're opening the form on a field value which is unique in your database, I'm confused on why you need to use the OpenArgs passing it multiple values such as the ID and the FirstName and the LastName. Typically you would only need to use the WHERE clause passing it just the unique ID value.

    I really never use the OnDirty event but I believe you would want to utilize it when for example, a combobox value changes or you move to another page in a tab control. But since you typically use the AfterUpdate event of a combobox or the OnChange event of a tab control, the OnDirty event seems reduntant. I believe this event was added in starting with MSAccess 2000 and is again, something I've never really found the need to ever use. But maybe someone else who has used it can explain it's purpose a little better.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The problem is in the logic of the code. You open the form empBen with a statement that has a valid OpenArgs clause. Stepping thru the code:

    The first line is If Not IsNull(Me.OpenArgs) Then

    So....

    OpenArgs = Me.ID & ";" & Me.First_Name & ";" & Me.Last_Name
    IsNull(Me.OpenArgs) = FALSE
    Not IsNull(Me.OpenArgs) = TRUE

    Therefore, the lines inside the If..End If statements are executed, creating a new record each time.

    Like pkstormy suggested, it would be better to use something like

    DoCmd.OpenForm "MyFormName", , , "[IDField] = " & Me.EmpID

    where EmpID is a long and the (unique) ID number of an employee.


    When the form "empBen" is opened, the underlying query would limit the records to the EmpID based on the Where clause from the "OpenForm" statement.

    Code in the FormOpen (or Load) event would check for records. If one or more were in the recordset, they would be displayed. If no records matched to EmpID, the code to create a new record would run.

    HTH
    ----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #7
    mwabbe is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    79
    so i changed some stuff and now when i click my button it creates a new record everytime but has the right data in it is there a way to check against exsisting data to do an if ... then dont do new. is there a way thats not to complex

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Form not passing data to query
    By bobfin in forum Queries
    Replies: 13
    Last Post: 08-11-2010, 05:28 AM
  2. Passing a form name to a subroutine
    By trb5016 in forum Programming
    Replies: 0
    Last Post: 02-01-2010, 12:03 PM
  3. Passing Criteria to IN statement from Form
    By stephenaa5 in forum Queries
    Replies: 1
    Last Post: 10-09-2009, 04:02 AM
  4. Passing data to sequential form fields
    By jeepfamilyva in forum Forms
    Replies: 0
    Last Post: 06-28-2009, 11:04 AM
  5. Passing a variable to a form
    By cjamps in forum Forms
    Replies: 0
    Last Post: 03-02-2009, 05:32 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums