Insert Into Table

[DMT Dave]

Hi Guy's would you be kind enough to tell me where this is wrong please ?

I am outputting 1 single record into tblJobSheets2 (this is to combine the quantity into 1 new record) there maybe 7 records for DelNo

Then trying to run an Insert SQL in the tblJobSheets

All is looking good apart from a refresh in the table (tblJobSheets), the data isn't transferring ?



The iJS (Job Sheet Number) should match in both tables

Code

If Forms!frmMainMenu!frmIndex3!txtCurrentSheet <> "" TheniJS = Forms!frmMainMenu!frmIndex3!txtCurrentSheet
If Not IsNull(strVehicle = DLookup("Vehicle", "tblJobSheets2", "[Driver] = '" & strDriver & "' And [DelDate] = #" & dtDelDate & "# And [DelNo] = " & intDelNo)) Then
    DoCmd.CancelEvent
    Else
    Set rs2 = CurrentDb.OpenRecordset("Select * From tblJobSheets2")
    strItems = DCount("DelTo", "tblAssign", "[PostCode] = '" & strPC & "' And [DeliveryDate] = #" & dtDelDate & "#")
        If strItems >= 1 Then
        strAddDel = DCount("DelTo", "tblAssign", "[PostCode] = '" & strPC & "' And [DeliveryDate] = #" & dtDelDate & "#") & " " & "Lifts"
        End If
        If strItems = 1 Then
        strAddDel = DCount("DelTo", "tblAssign", "[PostCode] = '" & strPC & "' And [DeliveryDate] = #" & dtDelDate & "#") & " " & "Lift"
        End If
        If IsNull(strEntCode = DLookup("EntryCode", "tblDealers", "[PostCode] = '" & strPC & "'")) Then
        strEntCode = ""
        Else
        strEntCode = DLookup("EntryCode", "tblDealers", "[PostCode] = '" & strPC & "'")
        End If
        If IsNull(strExtCode = DLookup("ExitCode", "tblDealers", "[PostCode] = '" & strPC & "'")) Then
        strExtCode = ""
        Else
        strExtCode = DLookup("ExitCode", "tblDealers", "[PostCode] = '" & strPC & "'")
        End If
    With rs2
    .AddNew
    !IDNumber = iJS
    !DelDate = dtDelDate
    !Driver = strDriver
    !Vehicle = strVehicle
    !DelNo = intDelNo
    !DelTo = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    !Items = strAddDel
    !EntCode = strEntCode
    !ExtCode = strExtCode
    .Update
    .Close
    End With
    
    Forms!frmMainMenu!frmIndex3!txtDropNo = Me.DelNo
    
    
    strSQL = "INSERT INTO tblJobSheets ( Driver, DelDate, Del" & intDelNo & ", CartonQty" & intDelNo & ", EntCode" & intDelNo & ", ExCode" & intDelNo & " ) " _
        & "SELECT tblJobSheets2.Driver, tblJobSheets2.DelDate, tblJobSheets2.DelTo, tblJobSheets2.Items, tblJobSheets2.EntCode, tblJobSheets2.ExtCode " _
        & "From tblJobSheets2 " _
        & "WHERE (((tblJobSheets2.IDNumber)= " & iJS & ") AND ((tblJobSheets2.DelNo) = " & intDelNo & "));"
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    Debug.Print strSQL
    DoCmd.SetWarnings True
    
End If
End If

Immediate Window

INSERT INTO tblJobSheets ( Driver, DelDate, Del7, CartonQty7, EntCode7, ExCode7 ) SELECT tblJobSheets2.Driver, tblJobSheets2.DelDate, tblJobSheets2.DelTo, tblJobSheets2.Items, tblJobSheets2.EntCode, tblJobSheets2.ExtCode From tblJobSheets2 WHERE (((tblJobSheets2.IDNumber)= 5219) AND ((tblJobSheets2.DelNo) = 7));

[Welshgasman]

Does the select work on it's own?

I wish your indentation was a lot better.

[DMT Dave]

Thanks WGM, what would be the best way to test that ?

Do another query using select without append ?

[DMT Dave]

Im thinking is it the WHERE clause should be & "WHERE (((tblJobSheets.IDNumber)= " & iJS & ") AND ((tblJobSheets2.DelNo) = " & intDelNo & "));"

not & "WHERE (((tblJobSheets2.IDNumber)= " & iJS & ") AND ((tblJobSheets2.DelNo) = " & intDelNo & "));"

[Welshgasman]

No, just copy the relevant output of the debug and paste into a sql window.
Then copy all of it and do the same, if incorrect, it should highlight where.

Only you know your data.?, i am just looking at the syntax and the debug.print looks OK (to me at least?), but test anyway.

[Gicu]

It looks to me that your tblJobSheets table is a "summary" table not normalized (containing 7 sets of fields) so you would only append (insert) the first one (iJS=1) and you would UPDATE the rest (joining the two tables by the appropriate fields).

Cheers,

[Welshgasman]

I have just seen you say

I am outputting 1 single record into tblJobSheets2

yet you have

"INSERT INTO tblJobSheets

????

[DMT Dave]

Hi Vlad, ahh ok, so this SQL is copied from an SQL of an append query and changed the field names based on the delivery number

yes there are fields in tblJobSheets named Del1 , Del2, Del3 etc up to 10

tblJobsheets has got a IDNumber (Primary Key)

Once deliveries are assigned, it is looking up the Min IDNumber from tblJobSheets where the deldate is null (this is the next available record)

The assignment is counting records and merging fields from tblAssgin ie: Vlad-Town-PostCode-7 Items

If i assign all of Vlad's 7 records as drop number 1

the tblJobSheets2 will have next (available job sheet number) DelNo 1 Delto Vlad-Town-Postcode Items = 7 Items

Insert into tblJobSheets x 1 record into fields Del1, Name-Town-Postcode

If you were assigned to drop number 4

this would go into jblJobSheets Del4 field

[Gicu]

Sorry Dave but you are loosing me here, can you show us a couple of screen shots for both tables with related items? In tblJobSheets do you expect for each IDNumber (PK) to have all appropriate sets of fields populated (Del1 for Dave, Del2 for Vlad, etc.), which you will only achieve with a combination of insert (for Del1) and update (for the rest) or maybe with he elusive upsert query (https://stackoverflow.com/questions/6199417/upserting-in-ms-access). Or do you expect to end up with separate records for each DelNo (so only Del1 will be populated for Dave and the rest will be empty, Del2 for Vlad and the rest empty, etc.), which is what your current approach of only using inserts will do?

Cheers,

[DMT Dave]

So i'm trying to to tell IDNumber 5198 in tblJobSheets that in your Del (DelNo Field) if del4 then field del4 that the data needs to come from tblJobSheets2 where that also is 5198

[DMT Dave]

here is the data stored in tblJobSheets2



here where the lines are are where the data should transfer to, because drop 4 then into del4 field, if dave was drop2m, then field Del2

tblJobSheets

[DMT Dave]

Wait a minute guy's the data IS GOING to tblJobSheets, it's not updating to that IDNumber, so false alarm, my code is working but how do I ensure i am not updating blank IDNumber ?

Many thanks

[DMT Dave]

Do i need some kind of EDIT Record 5231 ? the testing records are going into the table just as a blank IDNumber

Do you think i need to change this

& "WHERE (((tblJobSheets

2.IDNumber)= " & iJS & ") AND ((tblJobSheets2.DelNo) = " & intDelNo & "));"

to this ?

& "WHERE (((tblJobSheets

.IDNumber)= " & iJS & ") AND ((tblJobSheets2.DelNo) = " & intDelNo & "));"

But then it won't look in tblJobSheets2 for that IDNumber ?

[Gicu]

Do i need some kind of EDIT Record 5231

Yes, that is what I've been saying, you only Insert once for the first record (Del1) for the rest you need to find that already existing record in tblJobSheets and update it; you do that via a UPDATE query based on the two tables where you join them by IDNumber.

Or you can do both using one "upsert" query that inserts the record if none is present or updates it if present:
https://stackoverflow.com/questions/...n-button-press

Cheers,

[DMT Dave]

Ahh thanks Vlad, much appreciated, will try your suggestion