Help with code which deals with string variables

[medium]

In the code below, I want the information that is entered from the input box to be written to two different tables. More accurately, I want all of the information to be written to a "check out" table for record purposes. Moreover, I want the information relating to Parts such as part number and quantity to relate to interact with the parts table. I have facilitated the latter with the code below. However, my attempt to write and save to the "Check Out" table has failed.

The code is below.

Public Function CheckOut()

'declare variables and assign address to object variables
Dim strClkNo As String, strPartNo As String, IntQuantity As Integer

Dim PartNo As ADODB.Connection, rstPartNo As ADODB.Recordset
Set PartNo = Application.CurrentProject.Connection
Set rstPartNo = New ADODB.Recordset

Dim Conn1 As ADODB.Connection, rstChckOt As ADODB.Recordset
Set Conn1 = Application.CurrentProject.Connection
Set rstChckOt = New ADODB.Recordset


'Open the Chckot recordset
rstChckOt.Open Source:="CheckOut", ActiveConnection:=Conn1, _
CursorType:=adOpenForwardOnly, LockType:=adLockPessimistic

'open the PartsNo recordset
rstPartNpen Source:="Parts", ActiveConnection:=PartNo, _
CursorType:=adOpenForwardOnly, LockType:=adLockPessimistic



'Enter Clock Number, Part Number and Quantity of that same part
strClkNo = InputBox(prompt:="Please enter or scan your clock number:", Title:="Scan or Enter your Clock Number&quot
strPartNo = InputBox(prompt:="Please enter or scan the part number of your part:", Title:="Scan or Enter Part Number&quot
IntQuantity = InputBox(prompt:="Using the keyboard, please enter the quantity of that part you are checking out:", Title:="Quantity of previous part number you are checking out&quot


'Work this section out and your program will work.
strClkNo = rstChckOt.Fields("ClockNumber&quot
strPartNo = rstChckOt.Fields("PartNumber&quot
IntQuantity = rstChckOt.Fields("Quantity&quot
'Save ChckOt1 recordset
rstChckOt.Update
'Close Chckot1 recordset
rstChckOt.Close

'Diassociate object variable from object
Set rstChckOt = Nothing



'Search for and subtract from Parts record in "Parts" table using rstPartNo
rstPartNo.Find criteria:="PartNumber = '" & strPartNo & "'"
rstPartNo.Fields("UnitsInStock&quot.Value = rstPartNo.Fields("UnitsInStock&quot.Value - IntQuantity

'Save changes to PartNo recordset
rstPartNo.Update

'Close PartNo recordset
rstPartNo.Close

'Diassociate object variable from object
Set rstPartNo = Nothing

End Function


Thanks for any help in advance.
Inquisitor

 

[lameid]

Looks like you simply need to assign your recordset fields equal to your string variables instead of the other way around...

i.e.
rstChckOt.Fields("ClockNumber&quot = strClkNo

Instead of
strClkNo = rstChckOt.Fields("ClockNumber&quot

 

[medium]

I had that in there before. However, I was still not getting any output to the checkout table.

 

[lameid]

You definitely need to change it back to what I suggested...
I've not done any ADO programming yet (Still using Access 97) but in DAO you have to use the edit method on a recordset before you can make changes... At the same time I think I read something about it working differently with ADO... anyway looking up the update method in help and related topics ought to clear things up.

 

[medium]

Will check out the help topic.


Just so you will know:
Changed it back to what you recommended and got:

Run-Time Error 3265

Item cannot be found in the collection corresponding to the requested name or ordinal.

Then the VB Editor Highlights
rstChckOt.Fields("IntQuantity&quot = IntQuantity

 

[lameid]

Your original code had the field name as quantity instead of intquantity ...

rstChckOt.Fields("Quantity&quot = IntQuantity

That may be it or you need to check your CheckOut table for the correct field name.

I was reading another thread, and the Edit method isn't needed in ADO.

 

[medium]

You are right the original code did have quantity in the quotation marks after rstChckOt.Fields... That was because I understood the rstChckOt.Fields to be if you will going to the Checkout table to inser those variables in the appropriate fields. I believe the field name is correct.

I changed it back to "Quantity" instead of "IntQuantity" like it should be. Thanks for catching that oversight on my part.

Didn't get any error messages when I ran the code after changing the above. However, there was still no output to the Check Out fields mentioned in the part of the code about which we have been talking. It does begin a new record, but since no new data is entered into the table, the record doesn't remain permanent. I can go to the "CheckOut" table immediately after running the code and there will be the date and time stamped into a record but no autonumber is assigned due to no data being entered into the other fields.

 

[cmmrfrds]

To answer a simple question. There is no .Edit in ADO. ADO already knows the recordset is updateable from the lock type and cursor type. On the subject of lock types, it is not a good idea to be doing a pessimistic lock on a table. Nobody else will be able to update the table. Change to an optimistic lock. Better yet, when doing updates/inserts use the execute method of the ADO connection object. Also, a cursor type of forward only is not updateable.

A simple example of what an Open might look like.
sql1 = "select * from dbo.Employees "
rs.Open sql1, connString, adOpenStatic, adLockOptimistic

Example of execute method.
sql1 = "insert into #myTempTable values(" & mykey & ",'" & textData & "')"
cn.Execute sql1

 

[medium]

Thanks for all the help cmmrfrds and lameid.

Question:
If there is no update in ADO, why is the Part table changed when I run the code?

 

[cmmrfrds]

In DAO to update a record the .edit was needed first, in ADO that is no longer necessary.

DAO.
rs.edit
rs!myfield = var1
rs.update

ADO.
rs!myfield = var1
rs.update