Adding data using VBA and form data (1 Viewer)

[Cowboy]

So I have a form with about 6 fields that will hold numeric values that need to go into a "transaction" table.

I have the form built and a combo box for searching for a specific item.

Basically, I want to go select an item from the combo box, have the fields populate with the current values, and enter new values into the fields - click a button, and the new values will be stored in my transaction table as a "stocktake".

I don't know how to write the query or store the new values.

Any help would be greatly appreciated.

If more explanation is need, I can try to explain the tables/form/db in more detail.

Thanks!

 

[PNGBill]

The data on your Form is in Text Box Controls. Each Control has a Name.

Your Code, behind a Command Button, will (simply) input into a table, what ever data is in the given control names.

To access the data you use something like Me.ControlName in your code.

The code can input the data either by Sql (Append Query) or rst or maybe a few other methods.

This code adds different sets of data to tables using both data on the form and other data that the sql selects.

May be a bit long winded as an example.

Private Sub CmdLawyersLetter_Click()
On Error GoTo Err_CmdLawyersLetter_Click
            
    Dim sqlString As String
    Dim ADPK As Integer
    Dim LetterID As Integer
    Dim strUserID As String
    
    ADPK = Me.ADPK
    LetterID = 10
       
       'Get current operator
    strUserID = Me.strUserID
    
    DoCmd.SetWarnings False
    
        'Collect Data and append record to TblLetterSent
    sqlString = "INSERT INTO TblLetterSent ( LID, MID, FullName, Fax, LIDOverdue, LetterType, LetterSubject ) " & _
        "SELECT TBLLOAN.LDPK, TBLACCDET.ADPK, [ADFirstName] & "" "" & [ADSurname] AS FullName, TBLACCDET.ADFax, QryLoanCurrentBalanceResult.LoanCurrentBalance, TblLetterOptions.LetterID, TblLetterOptions.LetterType " & _
        "FROM TblLetterOptions, QryLoanCurrentBalanceResult INNER JOIN (TBLACCDET INNER JOIN TBLLOAN ON TBLACCDET.ADPK = TBLLOAN.ADPK) ON QryLoanCurrentBalanceResult.LoanID = TBLLOAN.LDPK " & _
        "WHERE (((TBLLOAN.LDPK)=" & LDPK & ") AND ((TblLetterOptions.LetterID)=" & LetterID & "));"
    
      DoCmd.RunSQL sqlString    'Append Leter Details to TblLetterSent
      
         'Collect Data and append record to tblLoanComm
    sqlString = "INSERT INTO tblLoanComm ( LoanID, CommNotes, Operator, CommDate, CommTime ) " & _
        "SELECT TBLLOAN.LDPK, ""Sent "" & [TblLetterOptions].[LetterType] AS CommNotes, strUserID, Date() AS CommDate, Time() AS CommTime " & _
        "FROM TBLLOAN, TblLetterOptions " & _
        "WHERE (((TBLLOAN.LDPK)=" & LDPK & ") AND ((TblLetterOptions.LetterID)=" & LetterID & "));"
     
    DoCmd.RunSQL sqlString    'Append Loan Communication to tblLoanComm
    
        'Collect Data on Letter just printed and update the Letter Printed Flag
    sqlString = "UPDATE TblLetterSent SET TblLetterSent.LetterPrinted = 2 " & _
        "WHERE (((TblLetterSent.LID)=" & LDPK & ") AND ((TblLetterSent.LetterDate)=Date()) AND ((TblLetterSent.LetterPrinted)=1));"
        
    DoCmd.RunSQL sqlString    'Update TblLetterSent Letter Printed Flag
   
    DoCmd.SetWarnings True
            
Exit_CmdLawyersLetter_Click:
    Exit Sub

Err_CmdLawyersLetter_Click:
    MsgBox Err.Description
    Resume Exit_CmdLawyersLetter_Click
    
End Sub

 

[PNGBill]

this code uses rst to append data from the form to a table and then opens another form.

Private Sub CmdSaveNewApplication_Click()
On Error GoTo Err_CmdSaveNewApplication_Click

    
    If MemberID > 0 Then
    
       Dim rst As DAO.Recordset
   
       Set rst = CurrentDb.OpenRecordset("TBLAPPLOAN", dbOpenDynaset)
       rst.AddNew
       rst!APLACCDET = Me!MemberID
       rst!APLDTE = Me!APLDTE
       rst!APLSTAT = "1"
       rst!APLCOM = Me!APLCOM
       rst!RepayMethod = Me!RepayMethod
       rst!NextPayDate = Me!NextPayDate
       rst!NextLeave = Me!NextLeave
       rst!DateRequired = Me!DateRequired
       rst!ApplNet = Me!AplNetPay
       rst!AppLoanOption = Me!AppLoanOption
       rst!RepayFreq = Me!RepayFreq
       rst!PayslipAttached = Me!PayslipAttached
       rst!APLTER = Me!FrmNewLoanApplicationSubFormLoanOption.Form!Term
       rst!AppLoanOption = Me!FrmNewLoanApplicationSubFormLoanOption.Form!LOID
       rst!RepayAmt = Me!FrmNewLoanApplicationSubFormLoanOption.Form!Repay
       rst!APLAMT = Me!FrmNewLoanApplicationSubFormLoanOption.Form!Princ
       rst!EmpName = Me!FrmNewLoanApplicationSubFormMember.Form!EDName
       rst.Update
   
       rst.Close
       Set rst = Nothing
    
    End If
    
    MemberID = ""
    APLACCDET = 0
    PayslipAttached = No
    APLCOM = ""
    RepayMethod = "Payroll"
    NextPayDate = ""
    NextLeave = ""
    DateRequired = ""
    ApplNet = 0
    AppLoanOption = 0
    RepayFreq = "Fortnightly"
    AplNetPay = 0
    
    'DoCmd.Close
   
    Dim stDocName As String

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
  
    stDocName = "FrmLoanApplicationsMain"
    DoCmd.OpenForm stDocName
    Form_FrmLoanApplicationsMain.ApplicationNewLoan
    
    
    

Exit_CmdSaveNewApplication_Click:
    Exit Sub

Err_CmdSaveNewApplication_Click:
    MsgBox Err.Description
    Resume Exit_CmdSaveNewApplication_Click
    
End Sub

 

[Cowboy]

Thanks for the examples. I'll try to give it a shot and see where I get!

Thanks again.