Add record

Posted on 2011-02-21
Last Modified: 2012-06-21

I have this form in continuous forms format with a listing of records for  a Company.
To add a record, I have to press a button and it will add based on the EndUserID and ProjID of the record a user has clicked on.

The issue is that a user usually scrolls to the bottom of the form and clicks in the NEW RECORD row and starts to add data.  If done this way then the EndUserID and ProjID are not associated with that record and the record just added woudl be unassociated and would not appear under the EndUserID and ProjID.  

If a user was to click in the NEW RECORD row then in effect what I would be looking for is a solution to add the EndUserID and ProjID

Any ideas on how to do this?  I hope the explanation is sufficient..pls ask if need more details.

Thank you
here is the code under the ADD RECORD button: (I also have ON LOAD event in the form and will post)

Private Sub cmdAddRecord_Click()

        Dim lngEndUserID As Long
    Dim lngPrjID As Long

On Error GoTo cmdAddRecord_Click_Err

If Me.NewRecord Then
  MsgBox "You're already at a New Record"
  Exit Sub
End If

If Not IsNull(Me.EndUserID) Then
   lngEndUserID = Me.EndUserID
   lngPrjID = Me.ProjectID
End If

    On Error Resume Next
    DoCmd.GoToRecord , , acNewRec
    If (MacroError <> 0) Then
        MsgBox MacroError.Description, vbOKOnly, ""
    End If

Me.EndUserID = lngEndUserID
Me.ProjectID = lngPrjID


    Exit Sub

    MsgBox Error$
    Resume cmdAddRecord_Click_Exit
End Sub

Question by:pdvsa
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4

Author Comment

ID: 34944270
here is the ON LOAD event:
Private Sub Form_Load()

    If Nz(Me.OpenArgs, "") <> "" Then
    If Not Me.NewRecord Then
    Me.Filter = "[ProjectID] = " & Split(Me.OpenArgs, ";")(0) & " AND [EndUserID] = " & Split(Me.OpenArgs, ";")(1)
       Me.FilterOn = True
        '/this is a new record
        'MsgBox Split(Me.OpenArgs, ";")(2)

        Me.ProjectID = Split(Me.OpenArgs, ";")(0)
        Me.EndUserID = Split(Me.OpenArgs, ";")(1)
     End If
End If
End Sub

LVL 31

Expert Comment

ID: 34944359
The usual approach for this type of situation is to put code on the BeforeInsert event of the form, that would write the appropriate values to the fields.  This event fires when the first data is entered into the new record.
LVL 31

Accepted Solution

Helen_Feddema earned 500 total points
ID: 34944384
You seem to be getting the ProjectID and EndUserID value from the same record you are trying to write to.  Best to set these variables from the the Load event, and then write them from the BeforeInsert event.  That is, if they are supposed to be the same for all the records on the Continuous form.  We may need more information on how your tables and forms are set up.
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

LVL 31

Expert Comment

ID: 34944394
You could also use the form's Tag property to store the IDs, and get them from that property in the BeforeInsert event.

Author Comment

ID: 34944670
Helen, thanks,... this seems to be exactly the solution.
<You seem to be getting the ProjectID and EndUserID value from the same record you are trying to write to.  Best to set these variables from the the Load event, and then write them from the BeforeInsert event.
==> Yes and how do you suggest I set those values?  

thank you

Author Comment

ID: 34944677
I know you mentioned teh TAG property too....I am looking for the easiest...I have no experience with TAG.

Author Comment

ID: 34945615

I think I did it.  

I added, BeforeInsert:
        Me.ProjectID = Split(Me.OpenArgs, ";")(0)
        Me.EndUserID = Split(Me.OpenArgs, ";")(1)

and it seems to be working as if I pressed teh Add Record button.  

Let me know if you think I could have missed something.  

LVL 31

Expert Comment

ID: 34945658
Yes, that should do it.  

Author Comment

ID: 34945699
I applied that to 3 other forms and makes life a lot easier... what an easy fix.  thank you

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question