UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

2 Pages V   1 2 >  
Reply to this topicStart new topic
> Working On An Sql Insert / Update Combined, Office 2010    
 
   
robsworld78
post Feb 24 2012, 01:41 AM
Post #1

New Member
Posts: 19



Hi, I have a bit of an issue with SQL Update / Insert.

I have a form that runs an SQL insert when a field is popluated and it works fine. When this sql insert runs it can add anywhere from 1 new record to 50 new records depending whats on the form.

CODE
DoCmd.RunSQL "INSERT INTO Inventory (Product, OrderID, BarsLeftTemp, BarsGiven) " & _
"SELECT ProductID, " & [Forms]![CreateOrders]![OrdersOrdersSubform]![OrderID] & " AS OrderID, " & "BarsLeft, " & "BarsLeft " & _
"FROM VendorInventoryLevel " & _ "WHERE (BarsLeft>0 Or BarsLeft<0) AND VendorID=" & [Forms]![CreateOrders]![VendorID] & ";"


The insert is for details pertaining to an order. Now I have a button on the same form and when clicked I want it to run another SQL insert/update but don't know how to right the code.

The 2nd insert will contain exisiting records from the 1st insert as well as new records so it needs to update the existing and insert the new records, I can't have duplicates.

Example:

I start a new order and the 1st SQL insert runs and I get the following items.

Order# 235 (stored in orders table)

item1 - 10 (stored in ordersdetail table linked with orderID) (10 being quanity)
item2 - 10
item3 - 10
item4 - 10

Now when I press the button, if I have SQL insert like above it would do the following, if the quantity was 5 on these items. (it duplicates items)

item1 - 10
item1 - 05
item2 - 10
item3 - 10
item3 - 05
item4 - 10
item4 - 05
item5 - 05

and if i press the button with SQL update it does the following (only updates, didn't add item5)

item1 - 10, 05
item2 - 10,
item3 - 10, 05
item4 - 10, 05

I hope this makes sense in the end the final list should be as follows because it updated the items already on the order and inserted items not on the order, in this case being item5.

item1 - 10, 05
item2 - 10,
item3 - 10, 05
item4 - 10, 05
item5 - 05
Go to the top of the page
 
+
the_captain_slog
post Feb 24 2012, 03:47 AM
Post #2

UtterAccess Guru
Posts: 756
From: England - UK A small island north of France



you need to run 2 queries

1 insert new records
2 update existing records

looking at what you have written above you have already written these queries

just add them into the macro/vba for the button click event
Go to the top of the page
 
+
pere_de_chipstic...
post Feb 24 2012, 03:55 AM
Post #3

UtterAccess VIP
Posts: 8,602
From: South coast, England



Hi

Would like to understand your scenario better;

You state you want the update query to give you:
item1 - 10, 05
item2 - 10,
item3 - 10, 05
item4 - 10, 05
item5 - 05

This indicates to me that you have additional columns in your table to cater for the updates - is this correct? Do you actually want to add the additional quantities so the table looks like:
item1 - 15
item2 - 10
item3 - 15
item4 - 15
item5 - 05
Go to the top of the page
 
+
robsworld78
post Feb 24 2012, 04:04 AM
Post #4

New Member
Posts: 19



I think I know what you mean, but now I can't get my update query to work. It won't update all the records, only the first record. This is my code.

CODE
DoCmd.RunSQL "UPDATE Inventory SET BarsGivenExtra=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " & Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product = " & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

If I use just the orderID in the WHERE condition it updates all the records but all records get updated with the first record from the subform, its not record for record.

I've been trying to add WHERE and FROM to the UPDATE query but its not working. I get run-time error 3144, syntax error in UPDATE statement.

CODE
DoCmd.RunSQL "UPDATE Inventory (Product, OrderID, BarsGiven) " & _
"SELECT ProductID, " & [Forms]![CreateOrders]![OrdersOrdersSubform]![OrderID] & " AS OrderID, " & "BarsLeft " & _
"FROM InventoryLevelVansSetForTransfer " & _
"WWHERE OrderID = " & Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product = " & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

Right now I'm trying to convernt the "INSERT" from my original post to an "UPDATE"
Go to the top of the page
 
+
robsworld78
post Feb 24 2012, 04:06 AM
Post #5

New Member
Posts: 19



QUOTE (pere_de_chipstick @ Feb 24 2012, 04:55 AM) *
Hi

Would like to understand your scenario better;

You state you want the update query to give you:
item1 - 10, 05
item2 - 10,
item3 - 10, 05
item4 - 10, 05
item5 - 05

This indicates to me that you have additional columns in your table to cater for the updates - is this correct? Do you actually want to add the additional quantities so the table looks like:
item1 - 15
item2 - 10
item3 - 15
item4 - 15
item5 - 05


You're correct, I do have extra columns in my table for the 2nd update. They have to go in separate then added together, That is what I'm trying to accomplish. The 2nd part is easy or should be but the update I'm having trouble with.

This post has been edited by robsworld78: Feb 24 2012, 04:07 AM
Go to the top of the page
 
+
robsworld78
post Feb 24 2012, 04:21 AM
Post #6

New Member
Posts: 19



On this update query, now I have it so if I click on one record and hit the button it updates that record, then if i set focus on a different record and hit the button it updates that record and so on, how do I get it to do all the records at the same time? Thanks

CODE
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenExtra=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " & Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product = " & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

Go to the top of the page
 
+
pere_de_chipstic...
post Feb 24 2012, 04:26 AM
Post #7

UtterAccess VIP
Posts: 8,602
From: South coast, England



Hi

Unless you have a real need for it then the use of additional columns like this breaks normalisation rules. You should be able to add to an existing field with something like:

strSQL = "UPDATE YourTableName SET YourFieldName=YourFieldName + " & NZ(Me.YourFormControlName,0)
CurrentDb.Execute strSQL, dbFailOnError

hth
Go to the top of the page
 
+
datAdrenaline
post Feb 24 2012, 01:48 PM
Post #8

UtterAccess Editor
Posts: 16,511
From: Northern Virginia, USA



Even though I take a very small hit on efficiency, I use Recordsets to do the Add/Update combo operation. With something like this ...

CODE
Sub Foo()
    With CurrentDb.OpenRecordset("SELECT * FROM someTable WHERE uniqueId = " & Me.someControlName)
        If .EOF Then
            .AddNew 'Since a record was not found it must be added
            .Fields("uniqueIdField") = Me.someControlName
        Else
            .Edit
        End If
        .Fields("nameOfField1") = someValue
        .Fields("nameOfField2") = someOtherValue
        .Update
        .Close
    End With
End Sub


{hopefully it is obvious that this is Air Code and will likely need adjusting if you implement the logic behind it}
Go to the top of the page
 
+
robsworld78
post Feb 24 2012, 04:31 PM
Post #9

New Member
Posts: 19



thanks for the replies, hth I do need to store both numbers, need to know what came from where to fill the order and how much.

I've been playing with the code datAdrenaline sent, thanks for that but of course I'm having problems. I'm happy I was able to change it and it actually runs with no errors however it does about the same as what I had.

When my first insert query ran it inserted 3 new records into the order, the other subform which holds the records I want to get the update from has 4 records in it, 3 the same and 1 new one.

When I hit the button it only updates the record I have focus on, in either subform.

This is the code I'm using to make the recordset, I have it on the "new order details subform" under the "OnCurrent" event.

CODE
On Error Resume Next
With Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form.Recordset
On Error Resume Next
.FindFirst "Productid = " & Forms!CreateOrders!OrdersInventorySubform.Form.ProductID
If .NoMatch Then
End If
End With

If I remove that code then it doesn't matter what record I have focus set on it always updates the first record so the code is doing something. With this code the record I have the focus on gets updated, but only that record. Here's what I did to the code you gave me.

CODE
With CurrentDb.OpenRecordset("SELECT * FROM Inventory WHERE OrderID = " & Me.OrderID & " AND Product = " & Me.ProductID)
        If .EOF Then
            .AddNew 'Since a record was not found it must be added
            
            .Fields("OrderID") = Me.OrderID
        Else
            .Edit
        End If
        .Fields("BarsGivenExtra") = Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft
        .Update
        .Close
    End With

Thanks
Go to the top of the page
 
+
robsworld78
post Feb 26 2012, 08:13 PM
Post #10

New Member
Posts: 19



any ideas from anyone?
Go to the top of the page
 
+
datAdrenaline
post Feb 26 2012, 08:14 PM
Post #11

UtterAccess Editor
Posts: 16,511
From: Northern Virginia, USA



Can you post your db with fake data in it? ... It would likely be easier for me (us) to show you a way to accomplish your task.
Go to the top of the page
 
+
robsworld78
post Feb 26 2012, 10:06 PM
Post #12

New Member
Posts: 19



Well, I'm happy I figured it out. Not the cleanest solution but its what I came up with and it works.

Since my original code did work and just didn't do all the records I thought about playing with GoToRecord and SetFocus. Now I have it so the 2nd subform setsfocus and goes to first record, then the SQL update I have gets run, then it goes to next record and the update is run again, then to next record and so on. Of course it does nothing when it focuses on a record not in the new order and continues so that's good. Once it goes through all the records on subform 2 it runs an SQL update which comes from a query. I was able to make 3 queries, one has all the records from the 1st subform and the 2nd query has all the records from the 2nd subform, then on the 3rd query it only shows the records not on the 1st subform and inserts those remaining records.

Its all invisible and instant, I'm very happy it's working. Thanks for you help if you have any suggestions to improve this let me know. Here's the code.

Do you know how I would add some code that does the same as "Exit Sub".

What I would like is once it hits the error, "can't go to record" I would like it to stop processing the VBA at that point and continue reading the code again once it gets to the SQL update near the bottom of the code. Is that possible?

CODE
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

Forms!CreateOrders!CreateOrdersVansSetForTransfer.SetFocus
Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!VendorID.SetFocus
On Error Resume Next
DoCmd.GoToRecord , , acFirst
DoCmd.SetWarnings False

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenVan=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " & Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product = " & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

On Error Resume Next
DoCmd.GoToRecord , , acNext

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenVan=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " & Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product = " & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

On Error Resume Next
DoCmd.GoToRecord , , acNext

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenVan=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " & Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product = " & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

On Error Resume Next
DoCmd.GoToRecord , , acNext

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenVan=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " & Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product = " & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

On Error Resume Next
DoCmd.GoToRecord , , acNext

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenVan=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " & Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product = " & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

On Error Resume Next
DoCmd.GoToRecord , , acNext

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenVan=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " & Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product = " & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

On Error Resume Next
DoCmd.GoToRecord , , acNext

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenVan=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " & Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product = " & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

On Error Resume Next
DoCmd.GoToRecord , , acNext

DoCmd.RunSQL "UPDATE Inventory SET BarsGivenVan=" & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!BarsLeft & _
" WHERE OrderID = " & Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID & "  And Product = " & Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID

DoCmd.RunSQL "INSERT INTO Inventory (Product, OrderID, BarsGivenVan) " & _
"SELECT ProductID, " & [Forms]![CreateOrders]![OrdersOrdersSubform]![OrderID] & " AS OrderID, " & "BarsLeft " & _
"FROM CreateOrdersVanTransferAppendQueryMain "
DoCmd.SetWarnings True

Me.Requery


This post has been edited by robsworld78: Feb 26 2012, 10:10 PM
Go to the top of the page
 
+
MadPiet
post Feb 26 2012, 10:36 PM
Post #13

UtterAccess Ruler
Posts: 1,035



<snip>When I hit the button it only updates the record I have focus on, in either subform. </snip>

Of course. by definition, if you use AddNew, you have no access to the existing records. If you want to do that, your best best is to grab the identifying columns (make up the primary or surrogate key) and then run an update.

strSQL = "UPDATE MyTable SET Field1='SomeConstant', Field2 = '" & Me.txtTextField & "' WHERE PrimaryKey=" & Me.txtPrimaryKey & ";"
DBEngine(0)(0).Execute strSQL

Personally, I would at least print the value of strSQL somewhere and proof it before executing... or back up your table first.

That should get you started, though.
Go to the top of the page
 
+
datAdrenaline
post Feb 27 2012, 01:35 AM
Post #14

UtterAccess Editor
Posts: 16,511
From: Northern Virginia, USA



Without seeing more details of how you have things set up, its hard to provide you with advice on how to improve your code .... but, from what I gather, I think something like this will do (or get real close) to what you are after. Please take a look at the logic of the code as well as its looping technique of the records displayed in a Form object.

CODE
Sub foo()
    
    Dim rstTransfer As DAO.Recordset
    Set rstTransfer = Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form.RecordClone
    
    Dim lngOrderId As Long
    lngOrderId = Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID
    
    Dim lngProductId As Long
    lngProductId = Forms!CreateOrders!OrdersOrdersSubform.Form!ProductID
        
    Do Until rstTransfer.EOF
        
        With CurrentDb.OpenRecordset("SELECT * FROM Inventory WHERE OrderID = " & lngOrderId & " AND Product = " & lngProductId)
            
            If .EOF Then
                .AddNew 'Since a record was not found it must be added
                .Fields("OrderID") = lngOrderId
                .Fields("ProductID") = lngProductId
            Else
                .Edit
            End If
            
            .Fields("BarsGivenExtra") = rstTransfer.Fields("BarsLeft")
            
            .Update
            .Close
        
        End With
        
        rstTransfer.MoveNext
    
    Loop
    
    Forms!CreateOrders!OrdersOrdersSubform.Requery
    
End Sub


{REMEMBER ... this is AIR CODE!}
Go to the top of the page
 
+
robsworld78
post Feb 27 2012, 08:51 PM
Post #15

New Member
Posts: 19



Thanks MadPiet for trying, datAdrenaline did nail it though and I'm very impressed you got it so close. All I had to do was change a couple subform locations and first try with the code it worked, definitely wasn't expecting that. The code does make some sense but I need to study it more to fully understand it. Sure cleans up what I had, you see how long the code was that I had however that was only a 1/4 of it because I was going to put each snippet of code 50 times so it could handle up to 50 records which should never happen but now I know nothing will go wrong and it runs quicker of course.

Thanks so much datAdrenline, I appreciate it. Here's the final code, seems to work in all scenarios.

CODE
Dim rstTransfer As DAO.Recordset
    Set rstTransfer = Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form.RecordClone
    
    Dim lngOrderId As Long
    lngOrderId = Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID
    
    Dim lngProductId As Long
    lngProductId = Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID
        
    Do Until rstTransfer.EOF
        
        With CurrentDb.OpenRecordset("SELECT * FROM Inventory WHERE OrderID = " & lngOrderId & " AND Product = " & lngProductId)
            
            If .EOF Then
                .AddNew 'Since a record was not found it must be added
                .Fields("OrderID") = lngOrderId
                .Fields("Product") = lngProductId
            Else
                .Edit
            End If
            
            .Fields("BarsGivenVan") = rstTransfer.Fields("BarsLeft")
            
            .Update
            .Close
        
        End With
        
        rstTransfer.MoveNext
    
    Loop
    
    Forms!CreateOrders!OrdersInventorySubform.Requery


This post has been edited by robsworld78: Feb 27 2012, 08:53 PM
Go to the top of the page
 
+
robsworld78
post Feb 27 2012, 10:37 PM
Post #16

New Member
Posts: 19



ok, actually I was wrong, I knew it was to good to be true to run first try. I added to the code to a different button so it always ran off the old code.

I just put the code where it should go and it trips on the 2nd line saying runtime error 2465 Application-defined or object-defined error. I tried different subforms and strings but nothing.
Go to the top of the page
 
+
datAdrenaline
post Feb 28 2012, 11:18 AM
Post #17

UtterAccess Editor
Posts: 16,511
From: Northern Virginia, USA



This ....

Set rstTransfer = Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form.RecordClone

Should be ...

Set rstTransfer = Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form.RecordsetClone

-----

Sorry ... remember, I said it was AIR CODE (IMG:style_emoticons/default/dazed.gif) ... Have you tried to compile your VBA project? ... I would suggest that you set:

Option Explicit

At the top of the module, then try to Compile (Debug | Compile) and see how your app fares.
Go to the top of the page
 
+
robsworld78
post Feb 28 2012, 05:21 PM
Post #18

New Member
Posts: 19



I changed that and the code runs now without error but nothing happens, no records get added and no records get editted. Here's the code with some comments explaining where they are pointing to, maybe it will help.

CODE
Dim rstTransfer As DAO.Recordset
    Set rstTransfer = Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form.RecordsetClone 'This subform holds the records I want to transfer
    
    Dim lngOrderId As Long
    lngOrderId = Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID 'This subform holds the order info only
    
    Dim lngProductId As Long
    lngProductId = Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form!ProductID
        
    Do Until rstTransfer.EOF
        
        With CurrentDb.OpenRecordset("SELECT * FROM Inventory WHERE OrderID = " & lngOrderId & " AND Product = " & lngProductId) 'Table column names are OrderID and Product
                    
            If .EOF Then
                .AddNew 'Since a record was not found it must be added
                .Fields("OrderID") = lngOrderId 'Column in inventory table
                .Fields("Product") = lngProductId 'Column in inventory table
            Else
                .Edit
            End If
            
            .Fields("BarsGivenVan") = rstTransfer.Fields("BarsLeft") '1st column is where I want the edit to happen, 2nd column from Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form
            
            .Update
            .Close
        
        End With
        
        rstTransfer.MoveNext
    
    Loop
    
    Forms!CreateOrders!OrdersInventorySubform.Requery 'This subform holds the orders details, its linked to OrdersOrdersSubform via OrderID.


I do have option explicit on all my modules and compile from time to time, just ran it again and its good.
Go to the top of the page
 
+
datAdrenaline
post Feb 28 2012, 10:34 PM
Post #19

UtterAccess Editor
Posts: 16,511
From: Northern Virginia, USA



Ok ... its good the have the Option Explicit and such. So ... again trying to troubleshoot at this level is a challenge without the db ... it seems that ProductId should be coming from the RecordsetClone, and thus it could vary with each row (record) that populates the subform of records to be transfered. Also, its an import concept to understand with the code we're working on is that we are not modifying the "cell" in the datasheet view of the sub-form, we are modifying the record that populates that grid, so the column order of the grid won't effect the code.

BUT ... before we proceed with more code, what is the RecordSource property of the Form Object used in the SubForm control named "CreateOrdersVansSetForTransfer"? Is is a Table, Query, or SQL Statment that returns the same columns that are in the Inventory table?

Does that RecordSouce have the following columns:

<RecordSource property of the Form object used in the subform control named CreateOrdersVansSetForTransfer> ... I will just call this "VansSetForTransfer"
OrderId (Number/Long)
ProductId (Number/Long)
BarsGiven (Number/Long)

If so that source matches the destination columns of

Inventory
OrderId (Number/Long)
Product (Number/Long)
BarsGivenVan (Number/Long)


... right?


If that is the case, then we can actually reduce the code significantly by making the Jet/ACE database engine do most of the work


CODE
    Dim strSQL As String
    
    'Build a SQL statement that will UPDATE joined records, or ADD a new record if their is no match (the Jet db engine is pretty cool this way :) ).
    strSQL = "UPDATE VansSetForTransfer As vTbl LEFT JOIN Inventory ON vTbl.OrderId = Inventory.OrderId AND vTbl.ProductId = Inventory.Product" & _
                 " SET Inventory.OrderId = vTbl.OrderId, Inventory.Product = vTbl.ProductId, Inventory.BarsGivenVan = vTbl.BarsGiven"  

    'Execute the SQL statement.
    CurrentDb.Execute strSQL, dbFailOnError

    'Requery the Inventory subform control.
    Forms!CreateOrders!OrdersInventorySubform.Requery



But if that is not the case, lets go back to the recordset concept and shift some stuff in order to give this a go:

CODE
    Dim rstTransfer As DAO.Recordset
    Set rstTransfer = Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form.RecordsetClone 'This subform holds the records I want to transfer
    
    Dim lngOrderId As Long
    lngOrderId = Forms!CreateOrders!OrdersOrdersSubform.Form!OrderID 'This subform holds the order info only
      
    Do Until rstTransfer.EOF
        
        With CurrentDb.OpenRecordset("SELECT * FROM Inventory WHERE OrderID = " & lngOrderId & " AND Product = " & rstTransfer.Fields("ProductId")) 'Table column names are OrderID and Product
                    
            If .EOF Then
                .AddNew 'Since a record was not found it must be added
                .Fields("OrderID") = lngOrderId 'Column in inventory table
                .Fields("Product") = rstTransfer.Fields("ProductId") 'Column in inventory table
            Else
                .Edit
            End If
            
            .Fields("BarsGivenVan") = rstTransfer.Fields("BarsLeft") '1st column is where I want the edit to happen, 2nd column from Forms!CreateOrders!CreateOrdersVansSetForTransfer.Form
            
            .Update
            .Close
        
        End With
        
        rstTransfer.MoveNext
    
    Loop
    
    Forms!CreateOrders!OrdersInventorySubform.Requery 'This subform holds the orders details, its linked to OrdersOrdersSubform via OrderID.
Go to the top of the page
 
+
robsworld78
post Feb 28 2012, 11:00 PM
Post #20

New Member
Posts: 19



The recordsource for VansSetForTransfer is a query and because its tracked inventory levels there is no orderID in that query. The columns in that query I need are.

Query Name: InventoryLevelVansSetForTransfer
ProductID (Number/Long)
BarsLeft (Number/Long)

Destination columns in Inventory table are
OrderID (Number/Long)
Product (Number/Long) - combobox productID being bound
BarsGivenVan (Number/Long)

The orderID for the destination is located in the OrdersOrdersSubform

Thanks for all your help
Go to the top of the page
 
+

2 Pages V   1 2 >
Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 18th April 2014 - 01:11 PM