Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

Add record

Experts,

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
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    End If

Me.EndUserID = lngEndUserID
Me.ProjectID = lngPrjID

Me.Refresh

cmdAddRecord_Click_Exit:
    Exit Sub

cmdAddRecord_Click_Err:
    MsgBox Error$
    Resume cmdAddRecord_Click_Exit
   
End Sub

0
pdvsa
Asked:
pdvsa
  • 5
  • 4
1 Solution
 
pdvsaAuthor Commented:
here is the ON LOAD event:
Private Sub Form_Load()

    DoCmd.Maximize
   
   
    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
     Else
        '/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

0
 
Helen FeddemaCommented:
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.
0
 
Helen FeddemaCommented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Helen FeddemaCommented:
You could also use the form's Tag property to store the IDs, and get them from that property in the BeforeInsert event.
0
 
pdvsaAuthor Commented:
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
0
 
pdvsaAuthor Commented:
I know you mentioned teh TAG property too....I am looking for the easiest...I have no experience with TAG.
0
 
pdvsaAuthor Commented:
Helen,

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.  

pdvsa.
0
 
Helen FeddemaCommented:
Yes, that should do it.  
0
 
pdvsaAuthor Commented:
I applied that to 3 other forms and makes life a lot easier... what an easy fix.  thank you
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now