Insert Specific Record

[mike60smart]

Hi
I have a Continuous Form and want to be able to use the record selector to select
a specific record and then Insert/Append that record to another table.

How would I modify the attached example code to achieve this?

Private Sub cmdInsert_Click()
Dim dbsMydbs As DAO.Database
    Dim rstMyTable As DAO.Recordset
    If Me.Updated = True Then
    Me.Dirty = False
        Set dbsMydbs = CurrentDb
        Set rstMyTable = dbsMydbs.OpenRecordset("tblBankPaymentHistory")
        With rstMyTable
            .AddNew
            !MembershipNrID = Me.MembershipID
            !MemNo = Me.MemNo
            !AmountPaid = Me.Subs
            !DatePaid = Now()
            .Update
        End With

    MsgBox "Members Payments have been added.", vbInformation, "Complete"
Else
    MsgBox "No Payments have been added.", vbInformation, "InComplete"

End If
        rstMyTable.Close
        Set rstMyTable = Nothing
        Set dbsMydbs = Nothing
End Sub

****Edit
I know I must amend this line :-
If Me.Updated = True Then

But to what? How do I specifiy the current record selected in VBA???

 

[Gasman]

Me.ID ?
I would also open the recordset for append only or where 1=0.

No point opening a 100,000 recordset to add one record?

 

[mike60smart]

Maybe I should amend my requirement.

I have a Continuous Form and want to be able to use the record selector to select
a specific record and then Insert/Append that record to another table.

What would be the Vb to use in the On Click Event of a Command Button which would only append the 1 record selected
using the record Selector?

I have Googled but cannot find anything to give me for example:-

If Me.RecordSelected then
DoCmd.openquery "qryAppendRecord"
End If

Can anyone show me where this is wrong?

 

[Gasman]

So if you are on a particular record, then all the Me.WhateverThe ControlnameIs should apply, surely?
If you want to do it by a query, and not a recordset, you would use Form!Formname!WhateverThe ControlnameIs for each of the fields, would you not?

 

[mike60smart]

Hi Pat

Many thanks for your input on this.

Is there no simple method to determine that I have selected a specific record on the form using the Record Selector?

 

[Gasman]

Mike,
Why not put a test button on your form that debug.prints some of the controls on a record?
Then select a record and see what is produced?

 

[ebs17]

To complete edits:
If Me.Updated = True Then
Me.Dirty = False

If Me.Dirty Then Me.Dirty = False

 

[mike60smart]

Continuous forms are not different from Single view forms. The form's code ALWAYS references the current record? So of course you know what record is "current". I don't know where you are going with this. Do you want to use DAO to add the record, you can if you want.

Hi Pat
I have changed my code as shown below but as usual not making that much progress.

I put Break Points on Lines 20 to 50 and each line when checked is highlighted in yellow as expected and on hover with mouse Intellisense shows values.

When I place a breakpoint on Line 60 when checked it does not change colour from Red to Yellow. It just remains as Red and no values shown'

Am I yet again going down the wrong route?

Private Sub cmdAppend_Click()

10        On Error GoTo cmdAppend_Click_Error
          Dim rnSQL As String
          Dim rowIndex As Integer
          Dim rowValue As String
          Dim rowIsSelected As Integer
          Dim result As String
          Dim strAVLDID As String
        
          strAVLDID = Me.txtAVLDID
          ' ListBox row index clicked
20        rowIndex = Me.LstAvailable.ListIndex 'Intellisense gives the value of 1

          ' Row value clicked
30        rowValue = Me.LstAvailable.Column(1) 'Intellisense gives the correct value of Avld-02

          ' If row is selected return value is -1, if unselected return value 0
40        rowIsSelected = Me.LstAvailable.Selected(rowIndex) 'Intellisense gives a value of 0

50        If (rowIsSelected = -1) Then 'Intellisense gives a value of 0
60            rnSQL = "INSERT INTO Loads ([AVLDID], [CustomerCD], [AgentID], [PUCity], [PUSt], [DlvCity], [DlvSt], [CommodityCD], [Weight], [PUDate], [DlvDt], [Miles], [RateQty], [Rate], [Accys], [LdNote]) Values ( '" & strAVLDID & "' , '" & [Cust CD] & "', '" & [Agent CD] & "', '" & [PU City] & "','" & [Pu ST] & "', '" & [Dlv City] & "', '" & [Dlv St] & "', '" & [Commodity] & "', '" & [Weight] & "','" & [PU Date] & "', '" & [Dlv Date] & "', " & [Miles] & ", " & [Rate Qty] & ", " & [R] & ", '" & [Accys] & "', '" & [Ld Notes] & "');"
70            CurrentDb.Execute rnSQL

80        Else
90            '.....
100       End If
      
110       On Error GoTo 0
120       Exit Sub

cmdAppend_Click_Error:

130       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdAppend_Click, line " & Erl & "."

End Sub

Sorry about line 60. Was not sure how to break the code to new lines

 

[gemma-the-husky]

out of interest, why would you want to insert an entire record into a second table.
Unless you are archiving the record I can't see the point.

 

[mike60smart]

Hi Dave

The Continuous Form displays records from a Linked Excel Sheet.

The Op then wants to select 1 specific record at a time to Append to the Loads Table.

 

[Gasman]

We have gone from a record on a Continuous form, to a listbox now?

 

[mike60smart]

We have gone from a record on a Continuous form, to a listbox now?

Whoops.
I forgot to mention that. Sorry

 

[bastanu]

Mike, is the selection happening in a continuous form as you originally stated or in a listbox as you show now? If a listbox where are all those Values ([]) coming from? Show us a screenshot with your form please....

Cheers,

 

[gemma-the-husky]

Are you importing everything from the linked excel sheet, out of interest? Is it a process where there is some way the import process can be automated?

 

[mike60smart]

Hi Everyone

I have gone the route of using Pat's suggested Append Query.

It is now solved.
Thanks again