Adding A New Record

[Jamz]

Hi Guys,

Trying to figure out what im doing wrong here, I have a value from a record that I want to add to a new entry when a button is pressed... Now ive got the value into variable, and I know its there as I have a messagebox pop up to check, but when I try and insert the value into a new entry it does not populate, but the new entry is created.

Private Sub btn_clsewr_Click()
    Dim Blanket_Orders As DAO.Database
    Dim rstRequests As DAO.Recordset
    Dim rstWR As DAO.Recordset
    Dim RepPO
    Dim ProType
    
    Set Blanket_Orders = CurrentDb
    Set rstRequests = Blanket_Orders.OpenRecordset("Requests")
    Set rstWR = Blanket_Orders.OpenRecordset("WR")
    
    'vSql = "SELECT products.[ID] FROM products WHERE products.[ProductName]= '" & Me.ProductName & "';"
    'Set rs = Blanket_Orders.OpenRecordset(vSql, dbOpenDynaset, dbSeeChanges)
    
    ' Scan the records from beginning to each
    While Not rstRequests.EOF
    
        If rstRequests("WRID").Value = Forms![Manage Request].[ID] Then
        
        Set RepPO = rstRequests("ReplacementPO")
        Set ProType = rstRequests("ProductID")
          
            ' Then update the WRID and Taken values
            'rstRequests.Edit
            'rstRequests("ReplacementPO").Value = "123456789"
            'rstRequests.Update
            
            Dim Response
            Response = MsgBox(RepPO, vbYesNo)
            
            rstRequests.AddNew
            rstRequests("PO").Value = RepPO
            rstRequests.Update
            
      End If
        ' Move to the next record and continue the same approach
        rstRequests.MoveNext
    Wend

Any ideas?

 

[vbaInet]

So you search for a value in a table, save the value into a variable and try an update a field in another table with the variable's value, right?

Are you sure you're not duplicating data?

 

[Jamz]

So you search for a value in a table, save the value into a variable and try an update a field in another table with the variable's value, right?

Are you sure you're not duplicating data?

Essencially yes i am, but the database needs to do that, so we can back reference the numbers to see what a product was ordered under - I know its long winded way of doing it, but it works.... kinda

 

[vbaInet]

Kinda like for auditing purposes?

All you do is run an UPDATE query to update the ReplacePO and PO fields in the Requests table where the Criteria under WRID is equal to the reference to the control.

No need looping through a recordset.

 

[Jamz]

Yea for auditing... the loop is becuase each work request could have multipal items - so it loops through the DB to check to see if there are more then one item.

I use the rstRequests.Update - but this does not put any data into the field i create

 

[vbaInet]

A query will pick up the multiple items. I went through your code and it's clear that what you're doing can be easily and quickly done in an UPDATE query, one for each table to be updated.

 

[Jamz]

But the table isnt to be updated, a new record is to be created from a record that already exists

 

[vbaInet]

Alright, well the first part is an UPDATE query to update ReplacementPO and the second one will be am APPEND query. Give it a go and let us know if you need a hand.

 

[Jamz]

I was kinda following this guide:

http://www.functionx.com/vbaccess2003/howto/addrecord.htm

But doesnt seem to insert the data in RepPO

 

[vbaInet]

That's just a DAO example of how to add a new record to a table. It doesn't mean that's the recommended method.

 

[Jamz]

Any code to help me out? I'm rubbish at thinking for myself, but if someone else puts code up, i can figure out what it does.... Coming from a PHP coding background - quote new to VBA

 

[vbaInet]

I'm not asking you to write code Jamz. I used to do a bit of PHP Have you not used an UPDATE or APPEND query before?

 

[Jamz]

Only this kinda update:

rstRequests.AddNew
rstRequests("PO").Value = RepPO
rstRequests.Update

 

[vbaInet]

Ok. That's a convoluted method for such a simple update. What you're doing with the AddNew part will put your loop out of synch.

In any case, let's focus on the easier and more effecient method. What version of Access are you using?

 

[Jamz]

Ok. That's a convoluted method for such a simple update. What you're doing with the AddNew part will put your loop out of synch.

In any case, let's focus on the easier and more effecient method. What version of Access are you using?

Im using 2007 mate.

 

[vbaInet]

These should be a good start.

Update query:
http://office.microsoft.com/en-us/access-help/update-data-by-using-a-query-HA010076527.aspx

Append Query (i.e. to add a new record):
http://office.microsoft.com/en-us/a...ble-by-using-an-append-query-HA010076526.aspx

 

[Jamz]

Hi vbaInet - i see what you are saying there, it probably would have been easier to make the database that way, but this database has already been made and its huge, i'm just modifying it which how its already been made to add in a couple of new features, which is why im doing it in the code.

I take it i cant do it the way I'm trying then.

I've just sorted the code out here:

Option Compare Database
Option Explicit
Private Sub btn_clsewr_Click()
    Dim Blanket_Orders As dao.Database
    Dim rstRequests As dao.Recordset
    Dim Response
    Dim rs As dao.Recordset
    Dim vsql As String
    Dim reqid
 
    Set Blanket_Orders = CurrentDb
    Set rstRequests = Blanket_Orders.OpenRecordset("Requests")
 
    ' First Check To See If All Data Is Entered - And Cannot Close Till Delivered
    ' Scan the records from beginning to see if PO/Aries Missing
    While Not rstRequests.EOF
 
        If rstRequests("WRID").Value = Forms![Manage Request].[ID] Then
        Set reqid = rstRequests("ProductID")
 
            vsql = "SELECT products.[ReqReplenishment] FROM products WHERE products.[id]= " & reqid & ";"
            Set rs = Blanket_Orders.OpenRecordset(vsql, dbOpenDynaset, dbSeeChanges)
 
            ' Make Sure User Has Put Aries Order Number In
            If rs![ReqReplenishment] = True And IsNull(rstRequests("ReplacementAries").Value) Then
            Response = MsgBox("You Must Enter Your Aries Order Number")
            Exit Sub
            End If
 
            ' Make Sure User Has Put PO Number In
            If rs![ReqReplenishment] = True And IsNull(rstRequests("ReplacementPO").Value) Then
            Response = MsgBox("You Need To Enter PO For Replenishment")
            Exit Sub
            End If
 
            ' Make Sure User Has Put Delivery Date In
            If rs![ReqReplenishment] = True And IsNull(rstRequests("ReplacementDeliverDate").Value) Then
            Response = MsgBox("You Need To Enter Delivery Date of Replacement")
            Exit Sub
            End If
 
            ' Make Sure Users Goods Are Now In Stores
            If rs![ReqReplenishment] = True And rstRequests("ReplacementDeliverDate").Value > Date Then
            Response = MsgBox("You Can Only Close This Once All Goods Have Been Delivered")
            Exit Sub
            End If
 
        End If
        ' Move to the next record and continue the same approach
        rstRequests.MoveNext
    Wend
 
    ' House Keeping
    Set rstRequests = Nothing
    Set Blanket_Orders = Nothing
 
' -----------------------------------------------------------------------------------------------------------------
 
    ' If all the above is ok, we proceed to below
    ' Set start of file again
    Set Blanket_Orders = CurrentDb
    Set rstRequests = Blanket_Orders.OpenRecordset("Requests")
 
    Dim test
 
    ' Now we get the po addresses and insert new records
    While Not rstRequests.EOF
 
        Set test = rstRequests("ReplacementPO")
 
        If rstRequests("WRID").Value = Forms![Manage Request].[ID] Then
            Set reqid = rstRequests("ProductID")
            vsql = "SELECT products.[ReqReplenishment] FROM products WHERE products.[id]= " & reqid & ";"
            Set rs = Blanket_Orders.OpenRecordset(vsql, dbOpenDynaset, dbSeeChanges)
 
            If rs![ReqReplenishment] = True And Not IsNull(rstRequests("ReplacementPO").Value) Then
            Response = MsgBox("Something went wrong")
            rstRequests.AddNew
            rstRequests("PO") = test
            rstRequests("ProductID").Value = rstRequests("ProductID").Value
            rstRequests("DateDelivered").Value = rstRequests("ReplacementDeliverDate").Value
            rstRequests("Notes").Value = rstRequests("ReplacementNotes").Value
            rstRequests.Update
            End If
 
        End If
        ' Move to the next record and continue the same approach
        rstRequests.MoveNext
    Wend
 
    ' Tidy up
    rstRequests.Close
    rs.Close
    Set rstRequests = Nothing
    Set Blanket_Orders = Nothing
End Sub

So basically first i make sure the user has input all the fields that are required, once ive made sure of that, i create the new record, and try and update it with the fields the current record is on using rstRequests(), but the data wont get inserted

If i use the following rstRequests("PO") = "123456" that will work, but if i use either rstRequests("PO") = rstRequests("ReplacementPO") or even if i set rstRequests("ReplacementPO") in a variable this doesnt work either

 

[Jamz]

got it working a different way, a way im more familiar with

            DoCmd.SetWarnings (False)
            testsql = "INSERT INTO [Requests] (PO,ProductID,DateDelivered,Notes) VALUES ('" & repPO & "','" & repPID & "','" & repDel & "','" & repNTE & "')"
            DoCmd.RunSQL testsql
            DoCmd.SetWarnings (True)

 

[vbaInet]

got it working a different way, a way im more familiar with

            DoCmd.SetWarnings (False)
            testsql = "INSERT INTO [Requests] (PO,ProductID,DateDelivered,Notes) VALUES ('" & repPO & "','" & repPID & "','" & repDel & "','" & repNTE & "')"
            DoCmd.RunSQL testsql
            DoCmd.SetWarnings (True)

Precisely what I was telling you to do. The base of an Append Query is the SQL Insert Into statement.

Good work!

 

[Jamz]

lol - you knew i would figuire it out in the end - everything working spot on