How to copy Information to a new record on a different table

[greeni91]

Hi All,

I am in the middle of creating a database for maintenance scheduling and I have hit a bit of a brick wall.

I have a main form that will be used to say when and what maintenance activities have been done, but I am trying to copy some of that information to another table when I click on my command button.

This is how far I have got with the code... I need help trying to word the code to add the information I have selected to a new record in a table called "Archive".

Private Sub cmdComplete1_Click()
Dim LDate As Date
LDate = DateAdd("m", 1, [Me.txtNextDate1])

Me.txtLastDate1.Value = Me.txtNextDate1.OldValue

Dim Mach As Variant
Dim Check As Variant
Dim NDate As Variant
Dim DateCom As Variant
Dim Sign As Variant
Dim Note As Variant

Mach = Me!txtMachine.Value
Check = "Monthly"
NDate = Me!NextDate1.Value
DateCom = Me!txtDate.Value
Sign = Me!cboPersonnel1.Value
Note = Me!txtNote1.Value


End Sub

Any help would be greatly appreciated,

/Sandy

p.s. I need to have this database completed by tomorrow for our NADCAP audit...

[NeoPa]

greeni91:
p.s. I need to have this database completed by tomorrow for our NADCAP audit...

No pressure then :D

You'd need to share more information for a more detailed solution, but I'd consider something like the following approach :

Private Sub cmdComplete1_Click()
    Dim LDate As Date
    Dim strSQL As String
    Dim Mach As Variant, Check As Variant, NDate As Variant
    Dim DateCom As Variant, Sign As Variant, Note As Variant

    With Me
        LDate = DateAdd("m", 1, .txtNextDate1)
        txtLastDate1 = .txtNextDate1.OldValue

        strSQL = "INSERT INTO [TargetTable] " & _
                             "([Machine] " & _
                             ",[Check] " & _
                             ",[NDate] " & _
                             ",[DateCom] " & _
                             ",[Signature] " & _
                             ",[Note]) " & _
                 "VALUES      ('%M' " & _
                             ",'Monthly' " & _
                             ",#%N# " & _
                             ",#%D# " & _
                             ",'%S' " & _
                             ",'%T')"
        strSQL = Replace(strSQL, "%M", .txtMachine)
        strSQL = Replace(strSQL, "%N", Format(.txtNextDate1, "m/d/yyyy"))
        strSQL = Replace(strSQL, "%D", Format(.txtDate1, "m/d/yyyy"))
        strSQL = Replace(strSQL, "%S", .cboPersonnel1)
        strSQL = Replace(strSQL, "%T", .txtNote1)
        Call CurrentDB.Execute(strSQL)
    End With
End Sub

You will need to decide whether the control for Next Date is named [NextDate1] or [txtNextDate1] though. You will also need to check for the various assumptions I've made as to the types of the fields used and if any are missing.

[greeni91]

Thanks NeoPa,

Everything is working okay with the code but I was wondering if you could rephrase the last section:

#         strSQL = Replace(strSQL, "%S", .cboPersonnel1)
#         strSQL = Replace(strSQL, "%T", .txtNote1)

I need to have these so that it allows for Null values.

sorry I didn't mention it before,

Thanks Again,

/Sandy

[NeoPa]

greeni91:
I need to have these so that it allows for Null values.

When you say "allows for Null values" do you mean unentered values (Control = Null) would be stored away as nulls or as empty strings?

[greeni91]

Yes that is what I mean, you see all my controls are locked till a specific date and they can be completed early if need be.

So if they click the complete button I will update the table without a name and without any notes.

The way the code is written just now means that it will not allow me to append my information to the table because there is: "improper use of Null value"

/Sandy

[NeoPa]

You misunderstand me Sandy. Let me see if I can make the question clearer :

When you say "allows for Null values" do you mean unentered values (Control = Null) would be stored away as :

1. Nulls
2. Empty strings?

[greeni91]

Yes I do mean unentered values,

I would say whatever is easiest but my choice is Nulls

/Sandy

[NeoPa]

NeoPa:

Private Sub cmdComplete1_Click()
    Dim LDate As Date
    Dim strSQL As String
    Dim Mach As Variant, Check As Variant, NDate As Variant
    Dim DateCom As Variant, Sign As Variant, Note As Variant

    With Me
        LDate = DateAdd("m", 1, .txtNextDate1)
        txtLastDate1 = .txtNextDate1.OldValue

        strSQL = "INSERT INTO [TargetTable] " & _
                             "([Machine] " & _
                             ",[Check] " & _
                             ",[NDate] " & _
                             ",[DateCom] " & _
                             ",[Signature] " & _
                             ",[Note]) " & _
                 "VALUES      ('%M' " & _
                             ",'Monthly' " & _
                             ",#%N# " & _
                             ",#%D# " & _
                             ",'%S' " & _
                             ",'%T')"
        strSQL = Replace(strSQL, "%M", .txtMachine)
        strSQL = Replace(strSQL, "%N", Format(.txtNextDate1, "m/d/yyyy"))
        strSQL = Replace(strSQL, "%D", Format(.txtDate1, "m/d/yyyy"))
        strSQL = Replace(strSQL, "%S", .cboPersonnel1)
        strSQL = Replace(strSQL, "%T", .txtNote1)
        Call CurrentDB.Execute(strSQL)
    End With
End Sub

OK. Nulls is harder, but here goes.

Replace lines #22 - #23 with :

                             ",%S " & _
                             ",%T)"

and lines #27 - #28 with :

        strSQL = Replace(strSQL, "%S", _
                                 IIf(IsNull(.cboPersonnel1), _
                                     "Null", _
                                     "'" & .cboPersonnel1 & "'"))
        strSQL = Replace(strSQL, "%T", _
                                 IIf(IsNull(.txtNote1), _
                                     "Null", _
                                     "'" & .txtNote1 & "'"))

[greeni91]

Hi Again,

I am using this code on a different form for Repairs in my database now and I am getting a Run-Time Error '3134': syntax error in INSERT INTO statement.

I have checked my code against that which NeoPa provided me above and everything seems to be okay but it constantly returns this same error... can someone please help??

The code I have made is:

Private Sub cmdLog_Click()
     Dim strSQ As String
     Dim Mach As Variant, WTime As Variant, Comp As Variant
     Dim DateCom As Date, Sign As Variant

     With Me

         strSQL = "INSERT INTO [RepairsArchive] " & _
                              "([Machine] " & _
                              ",[Component] " & _
                              ",[WaitingTime] " & _
                              ",[CompletionDate] " & _
                              ",[Signed] " & _
                  "VALUES      ('%M' " & _
                              ",'%C' " & _
                              ",'%N' " & _
                              ",#%D# " & _
                              ",%S)"
         strSQL = Replace(strSQL, "%M", .txtMachine)
         strSQL = Replace(strSQL, "%C", .txtComponent)
         strSQL = Replace(strSQL, "%N", .txtWaitingTime)
         strSQL = Replace(strSQL, "%D", Format(.txtCompletionDate, "dd/mm/yyyy"))
         strSQL = Replace(strSQL, "%S", .cboSigned)


         Call CurrentDb.Execute(strSQL)
         MsgBox "Your Repair has been saved in the Archives!", vbOKOnly, "Repair Complete"
     End With
End Sub

Every time I click the command button associated with this code it highlights line 26.

Thanks

/Sandy

[NeoPa]

Sandy,

Pop the actual SQL in here (as line #26 basically says "Run this SQL")

See indented text below for how to go about this.

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

If the problem is still unclear then take the resultant SQL string (copied to clipboard in previous paragraph) and paste it into the SQL view of any query. From here you can try to switch to Design View. If that works, then you may try to run it (or simply switch to Datasheet View which shows data but doesn't apply any changes - for action queries).