How do I Update a Recordset? 1

[Gawlowski]

Here's the situtation:
I have data coming from an external tool which is populated into a "virtual" workspace called "MyData".
I want to take this data and update a field called "Begin_Weight" in my table, "tblMain".
There are only two fields in the table: Item_ID (which is the primary key) and Begin_Weight.

The problem is, I do not know how to write a module which would do the following:

- Identify which record is to be updated. (I visulize the user being prompted to enter the Item_ID.). Then MyData would populate the Begin_Weight field for this record.

- If the Item_ID is not found, then we will need to create a new record. Upon creating the new record, the user will be prompted to enter the Item_ID then, MyData would populate the Begin_Weight field for this new record.

Does this make sense? It seems so simple, but, I just can't get it. Can anyone assist?
I appreciate any feedback.
Thanks, and have a great day. Gawlowski

If I add a new record, I need the user to input the Record name, as well as populate the respective field within that new record with MyData.

[sig][/sig]

 

[TheInsider]

Hi,
I wrote this code for you it should be what you are looking for:

Private Sub FindOrAdd()
Dim pdbDatabase As Database, precCurrent As Recordset, plngItemID As Long, _
pintBeginWeight As Integer, pblnFound As Boolean

'Get the item ID and beginning weight
plngItemID = InputBox("Enter the item ID&quot
pintBeginWeight = InputBox("Enter the beginning weight&quot

'CurrentDb or use OpenDatabase() method for external
Set pdbDatabase = CurrentDb
Set precCurrent = pdbDatabase.OpenRecordset("tbdTable&quot

'Flood the recordset with records (required)
precCurrent.MoveLast
'Move back to the first record
precCurrent.MoveFirst

'Set the found flag equal to "False"
pblnFound = False

While Not (precCurrent.EOF)
If (precCurrent![Item_ID] = plngItemID) Then
'Use pdbDatabase.Execute "" if using Visual Basic instead of VBA
DoCmd.RunSQL "UPDATE tbdTable SET tbdTable.BeginningWeight = " _
& pintBeginWeight & " WHERE (((tbdTable.Item_ID) = " _
& plngItemID & &quot)"

pblnFound = True
End If

precCurrent.MoveNext
Wend

If Not (pblnFound) Then DoCmd.RunSQL "INSERT INTO tbdTable (Item_ID, BeginningWeight) VALUES (" _
& plngItemID & ", " & pintBeginWeight & &quot"

precCurrent.Close
Set pdbDatabase = Nothing
End Sub

Sorry, it didn't paste that well. You'll probably need to change thing like the weight from an integer to a floating point number... etc. Let me know what you think.
Rob Marriott
CCTC1
rob@career-connections.net [sig][/sig]

 

[TheInsider]

Oh, you can also use the Recordset methods:

.Edit
.Update
.AddNew

You would call MyRec.Edit on the current record in the recordset, make your change like MyRec![MyNum] = 4, then call the MyRec.Update.... These are alternatives to the embedded SQL statements that I used in the code above.
Rob Marriott
CCTC1
rob@career-connections.net [sig][/sig]

 

[Gawlowski]

Mr. Marriot:
THANK YOU SO VERY MUCH FOR YOUR ASSISTANCE!!!
I hope I am not overextending your guidance. Because of your generosity, I am extremely so close to completing this code. The only problem is, after entering an Item_ID, the code prompts me for a "Paramter Value". I do not know what is causing this. I have copied the entire module for your review. If you can assist, I owe you big time, if you can't, just let me know.
Thanks, again. Have a great day, Gawlowski
*********
Function GetDataBegin_Weight()
'create variables
Dim ChannelNumber, MyData As String
'open a link to WinWedge
ChannelNumber = DDEInitiate("WinWedge", "Com1&quot
'get data from field(2)
MyData = DDERequest(ChannelNumber, "FIELD(2)&quot
'terminate the link
DDETerminate ChannelNumber
'if no data then quit
If Len(MyData) = 0 Then Exit Function

'Data from Field(2) in WinWedge is now in the variable "MyData".
'The following code updates or adds a new record to tblMain and stores the
'data in a field named "Begin_Weight"
Dim pdbDatabase As Database, precCurrent As Recordset, plngItemID As String, _
pintBeginWeight As Integer, pblnFound As Boolean

'Get the item ID and beginning weight
plngItemID = InputBox("Enter the Item ID&quot
pintBeginWeight = MyData

'CurrentDb or use OpenDatabase() method for external
Set pdbDatabase = CurrentDb
Set precCurrent = pdbDatabase.OpenRecordset("tblMain&quot

'Flood the recordset with records (required)
precCurrent.MoveLast
'Move back to the first record
precCurrent.MoveFirst
'Set the found flag equal to "False"
pblnFound = False

While Not (precCurrent.EOF)
If (precCurrent![Item_ID] = plngItemID) Then
DoCmd.RunSQL "UPDATE tblMain SET tblMain.Begin_Weight = " _
& pintBeginWeight & " WHERE (((tblMain.Item_ID) = " _
& plngItemID & &quot)"

pblnFound = True
End If

precCurrent.MoveNext
Wend

If Not (pblnFound) Then
DoCmd.RunSQL "INSERT INTO tblMain (Item_ID, Begin_Weight) VALUES (" _
& plngItemID & "," & pintBeginWeight & &quot"

precCurrent.Close
Set pdbDatabase = Nothing
End If
End Function [sig][/sig]

 

[TheInsider]

Hi,
Just so we can pin-point the problem, could you set a break point in the code and tell me which line is prompting you for the "Parameter Value". The only line that should be asking for any parameter values is the line with the call to InputBox(). This will prompt you for a paremeter value, but should say "Enter the Item ID". You will probably want to remove the call to InputBox() and replace it with an object referrence on the form that contains the data ie. "plngItemID = Me.txtItemID" (for a text box value) or "Me![Item_ID]" (for the form's current record's Item_ID field value).

Also, If you run an embedded SQL statement that contains a parameter query, you will get an error message saying that the query requires parameters, but it will not prompt you to enter them.
Rob Marriott
CCTC1
rob@career-connections.net [sig][/sig]

 

[TheInsider]

(Forget my last posting... what can I say; its Monday morning =) )
Hi,
Sorry, I'm thinking of Visual Basic not VBA. The ".RunSQL" method can prompt you to enter a parameter value. The ".Execute" method in Visual Basic 3-6 will not. Your Item ID field is a string, so you must change the following code from:

DoCmd.RunSQL "UPDATE tblMain SET tblMain.Begin_Weight = " _
& pintBeginWeight & " WHERE (((tblMain.Item_ID) = " _
& plngItemID & &quot)"

to:

DoCmd.RunSQL "UPDATE tblMain SET tblMain.Begin_Weight = " _
& pintBeginWeight & " WHERE (((tblMain.Item_ID) = '" _
& plngItemID & "'))"

You will notice the "'" I have added, you must place these here also:

DoCmd.RunSQL "INSERT INTO tblMain (Item_ID, Begin_Weight) VALUES ('" _
& plngItemID & "'," & pintBeginWeight & &quot"

I'm sure that it is one of those SQL statements. It is acting as if one of the variables we have declared in the code or a field is not there. When I wrote the code, it ran fine on my end. So it must be something that you have changed to get it to adapt to your database. Add the "'" quotes and set the breakpoint to find the line of code causing the parameter to prompt, then let me know.
Rob Marriott
CCTC1
rob@career-connections.net [sig][/sig]