Results 1 to 12 of 12
  1. #1
    Join Date
    May 2002
    Location
    wakefield, ri
    Posts
    35

    Unanswered: Save a Record to data Table using a pop-up Form

    At the BeforeInsert Event, I have a pop-up form prompt the user "Do you want to save the record?".

    The code for the "Yes" On Click Event is as follows:

    Private Sub btnSaveRecord_Click()
    On Error GoTo Err_btnSaveRecord_Click
    Me.CurrentRecord = "frmDataForm"
    RunCommand acCmdSaveRecord
    DoCmd.Close
    Exit_btnSaveRecord_Click:
    Exit Sub
    Err_btnSaveRecord_Click:
    MsgBox Err.Description
    Resume Exit_btnSaveRecord_Click
    End Sub

    I get a "type mismatch" error. I would very much appreciate any coding help that would do the record saving function. Thanks!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Re: Save a Record to data Table using a pop-up Form

    Originally posted by gatewoodps
    Me.CurrentRecord = "frmDataForm"
    What is that? Are you trying to set the current record to the current record of frmDataForm? If so, you need to be explicit. Right now you're trying to set a current record property to a string, which is producing an error. Try:

    Me.CurrentRecord = forms!frmDataForm.CurrentRecord

  3. #3
    Join Date
    May 2002
    Location
    wakefield, ri
    Posts
    35

    Saving a record using a popup Form

    I'm trying to save the current record of frmDataForm with a pop-up (frmSaveRecord).

    Tried "Me.CurrentRecord = forms!frmDataForm.CurrentRecord" instead and got an error.

    Appreciate any further guidance here. Thanks

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Saving a record using a popup Form

    Originally posted by gatewoodps
    I'm trying to save the current record of frmDataForm with a pop-up (frmSaveRecord).

    Tried "Me.CurrentRecord = forms!frmDataForm.CurrentRecord" instead and got an error.

    Appreciate any further guidance here. Thanks
    What error was it? Also regarding your request on code for saving records, there are numerous ways ( and methods ) for saving ... Which do you prefer? ADO? DAO?

    I personally do ADO to eliminate potential multi-user conflicts ...

  5. #5
    Join Date
    May 2002
    Location
    wakefield, ri
    Posts
    35
    Sorry, not an error. A popup window appears with the following message:
    You entered an expression that has an invalid reference to the property CurrentRecord.

    The code behind the "yes" button on the pop-up form is:
    Private Sub btnSaveRecord_Click()
    On Error GoTo Err_btnSaveRecord_Click
    Me.CurrentRecord = Forms!frmDataForm.CurrentRecord
    RunCommand acCmdSaveRecord
    DoCmd.Close
    Exit_btnSaveRecord_Click:
    Exit Sub
    Err_btnSaveRecord_Click:
    MsgBox Err.Description
    Resume Exit_btnSaveRecord_Click
    End Sub

    Frankly, I don't know enough about ADO or DAO to make an intelligent choice. I will have multiple users using their own front ends linked to a back end database so ADO may be appropriate. That is the reasoning for saving records and a sequential Item number assigned.

    Hope this helps and thanks again.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by gatewoodps
    Sorry, not an error. A popup window appears with the following message:
    You entered an expression that has an invalid reference to the property CurrentRecord.

    The code behind the "yes" button on the pop-up form is:
    Private Sub btnSaveRecord_Click()
    On Error GoTo Err_btnSaveRecord_Click
    Me.CurrentRecord = Forms!frmDataForm.CurrentRecord
    RunCommand acCmdSaveRecord
    DoCmd.Close
    Exit_btnSaveRecord_Click:
    Exit Sub
    Err_btnSaveRecord_Click:
    MsgBox Err.Description
    Resume Exit_btnSaveRecord_Click
    End Sub

    Frankly, I don't know enough about ADO or DAO to make an intelligent choice. I will have multiple users using their own front ends linked to a back end database so ADO may be appropriate. That is the reasoning for saving records and a sequential Item number assigned.

    Hope this helps and thanks again.
    Ok then ... An ADO Example (my code) of saving a record:

    Code:
    Private Sub SaveButton_Click()
    On Error GoTo Err_SaveButton_Click
    
        If Not FieldValidations Then Exit Sub
        
        Dim InTrans As Boolean
        Dim NewPaymentID As Long
        Dim NewPmtAmt As Currency, NewLabAmt As Currency, NewPurAmt As Currency
        
        ' Check if Payment is non-zero.
        NewPmtAmt = CCur(AmtTxt.Value)
        If NewPmtAmt = 0 Then
            MsgBox "Payment amount is $0.00. This action is cancelled.", vbInformation, "System Monitor"
            Exit Sub
        End If
        NewLabAmt = 0
        If LaborTxt.Value & "" <> "" Then NewLabAmt = CCur(LaborTxt.Value)
        NewPurAmt = 0
        If PurchasesTxt.Value & "" <> "" Then NewPurAmt = CCur(PurchasesTxt.Value)
        If (NewLabAmt + NewPurAmt) <> NewPmtAmt Then
            MsgBox "The applied labor and purchases amounts do not equal the payment amount.", vbExclamation, "System Monitor"
            Exit Sub
        End If
    
        InTrans = False
        MyConnect.BeginTrans
        InTrans = True
        NewPaymentID = GetNextPaymentID
        If NewPaymentID < 1 Then
            MyConnect.RollbackTrans
            Exit Sub
        End If
        SQLString = "SELECT * FROM Payments WHERE (1=0);"
        MyRecSet.Open SQLString, MyConnect
        ' Add the Payment record
        MyRecSet.AddNew
        ' Payment ID
        MyRecSet.Fields(0).Value = NewPaymentID
        ' W.O. #
        MyRecSet.Fields(1).Value = WO_ComboBox.Value
        ' Payor
        MyRecSet.Fields(2).Value = Left(PayorTxt.Value & "", 64)
        ' Check #
        MyRecSet.Fields(3).Value = Left(CheckTxt.Value & "", 16)
        ' Labor Portion
        MyRecSet.Fields(4).Value = 0
        If LaborTxt.Value & "" <> "" Then MyRecSet.Fields(4).Value = CCur(LaborTxt.Value)
        ' Purchases Portion
        MyRecSet.Fields(5).Value = 0
        If PurchasesTxt.Value & "" <> "" Then MyRecSet.Fields(5).Value = CCur(PurchasesTxt.Value)
        ' Payment Amount
        MyRecSet.Fields(6).Value = CCur(AmtTxt.Value)
        ' Received Date
        MyRecSet.Fields(7).Value = Date
        ' Refund
        MyRecSet.Fields(8).Value = RefundCheckBox.Value
        ' Notes
        MyRecSet.Fields(9).Value = Left(NotesTxt.Value & "", 64)
        ' Last Modified
        MyRecSet.Fields(10).Value = Now
        ' Owner (to blame)
        MyRecSet.Fields(11).Value = MyUserID
        MyRecSet.Update
        MyRecSet.Close
        MyConnect.CommitTrans
    
        ' Clear everything out. (Reset)
        RefundCheckBox.Value = False
        AccountComboBox.Value = ""
        WO_ComboBox.Value = ""
        TitleLbl.Caption = ""
        OpenLaborLbl.Caption = ""
        OpenPurchasesLbl.Caption = ""
        
        PayorTxt.Value = ""
        CheckTxt.Value = ""
        AmtTxt.Value = ""
        LaborTxt.Value = ""
        LaborTxt.Enabled = True
        PurchasesTxt.Value = ""
        NotesTxt.Value = ""
        AccountComboBox.SetFocus
        
    Exit_SaveButton_Click:
        Exit Sub
    
    Err_SaveButton_Click:
        If InTrans Then MyConnect.RollbackTrans
        If MyRecSet.State <> adStateClosed Then MyRecSet.Close
        MsgBox Err.Number & ": " & Err.Description
        Resume Exit_SaveButton_Click
    End Sub
    ... And I've been told that my code can cause severe headaches and/or even brain trauma ...

  7. #7
    Join Date
    May 2002
    Location
    wakefield, ri
    Posts
    35
    Seems way too complicated and not applicable to my application.

    Basically, all I want is to do the function of the menu item Record-Save using a pop-up.

    Isn't there some simple code for saving a record using a pop up Form???

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Something to think on ... With using a bound form, moving off the "new" record will save it.

  9. #9
    Join Date
    May 2002
    Location
    wakefield, ri
    Posts
    35
    I have multiple users using a front end database on their PCs linked to a back end database with just the data Tables.

    New record use the following code to get the next sequential "Item Number" (Item Number field default).

    Function Get_Next_Inum() As String
    Dim strMaxInum As String

    strMaxInum = DMax("[Inum]", "Item")
    Get_Next_Inum = str(CLng(strMaxInum) + 1)

    End Function

    I thought the best way a single user could keep the Item Number was to save the record to the table as soon as the first character is entered.

    Any ideas out there of an effective method?

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by gatewoodps
    I have multiple users using a front end database on their PCs linked to a back end database with just the data Tables.

    New record use the following code to get the next sequential "Item Number" (Item Number field default).

    Function Get_Next_Inum() As String
    Dim strMaxInum As String

    strMaxInum = DMax("[Inum]", "Item")
    Get_Next_Inum = str(CLng(strMaxInum) + 1)

    End Function

    I thought the best way a single user could keep the Item Number was to save the record to the table as soon as the first character is entered.

    Any ideas out there of an effective method?
    The best way is to have a separate table with a Unique # column that you poll and increment ...

  11. #11
    Join Date
    May 2002
    Location
    wakefield, ri
    Posts
    35
    Re: The best way is to have a separate table with a Unique # column that you poll and increment ...

    So how would you go about reserving a unique #?

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by gatewoodps
    Re: The best way is to have a separate table with a Unique # column that you poll and increment ...

    So how would you go about reserving a unique #?
    How about an example? Code:

    Code:
    Function GetNextTrackingNumber() As Long
        On Error GoTo Err_GNTN
        
        Dim NextTrackNum As Long
        Dim TrgRecSet As ADODB.Recordset
        
        Set TrgRecSet = New ADODB.Recordset
        
        TrgRecSet.CursorType = adOpenDynamic
        TrgRecSet.LockType = adLockOptimistic
        TrgRecSet.CursorLocation = adUseClient
        
        SQLString = "SELECT [Next Tracking #] FROM BillingSettings;"
        TrgRecSet.Open SQLString, MyConnect
        If TrgRecSet.BOF = False Then
            TrgRecSet.MoveFirst
            NextTrackNum = TrgRecSet.Fields(0).Value
            TrgRecSet.Fields(0).Value = NextTrackNum + 1
            TrgRecSet.Update
        End If
        TrgRecSet.Close
        Set TrgRecSet = Nothing
        
        GetNextTrackingNumber = NextTrackNum
        
    Exit_GNTN:
        Exit Function
        
    Err_GNTN:
        
        MsgBox Err.Number & ": " & Err.Description
        GetNextTrackingNumber = 0
        Resume Exit_GNTN
    End Function

Posting Permissions

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