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?

 

[Pat Hartman]

If you are selecting one record and do not need to copy any child records, then the simplest method is an append query. Create it using the query builder. Then you need to add two variables.

Where SomeField = Forms!yourform!SomeField ''' to select the record

Then, you need to know the FK of the parent in the target table because you need to change the FK field in the appended record.

Here's the querydef:

INSERT INTO tblquoteacc ( QuoteDetailID, ItemNo, EstID, ModelNo, Description, Qty, Price )
SELECT [EnterNewQuoteDetailID] AS Expr1, tblQuoteAcc.ItemNo, tblQuoteAcc.EstID, tblQuoteAcc.ModelNo, tblQuoteAcc.Description, tblQuoteAcc.Qty, tblQuoteAcc.Price
FROM tblQuoteAcc
WHERE (((tblQuoteAcc.QuoteDetailID)=[EnterOldQuoteDetailID]));

Here's the code that runs it:

 Set qd = db.QueryDefs!qCopyAppendQuoteAcc
                    qd.Parameters!EnterOldQuoteDetailID = Me.QuoteDetailID
                    qd.Parameters!EnterNewQuoteDetailID = NewQuoteDetailID
                    qd.Execute dbSeeChanges

In the query, you will see the two arguments and where they are used. One is named "EnterOldQuoteDetailID" and is used in the where clause. The other is "EnterNewQuoteDetailID" and is used in the Select clause.

I always use querydefs unless some part of the query is dynamic. You can do what you want. The part of this that people have trouble with is figuring out how to pass the arguments and the sample above shows how. You need to define the variable qd as a querydef. There may be some syntax that allows you to use some other way to reference the arguments but I'm OK with this one so I've never looked. This code is part of a larger procedure that appends the new quote so that is why the new quote id comes from a variable rather than the form. This append query is appending all the child records of the quote rather than just one specific record.

 

[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

 

[Pat Hartman]

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

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.

 

[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

 

[Pat Hartman]

I am confused about what you think should happen.

If the spreadsheet has a uniqe id, then just use an append query.

 

[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

 

[Pat Hartman]

Great. Glad that worked for you.