Function value into table

[RickRo]

Hello All,

I am having the worst time trying to figure out something that is so simple - but I just cant seem to get it.

I have a function that grabs the user name; I am trying to use that value as data for some fields. I just cant figure out how to write that data into a record. This is what I have:

Public Sub WritetblTest()
Dim RecSet As Recordset
Dim strTechName As String
strTechName = ReturnUserName()
Set RecSet = CurrentDb.OpenRecordset("tblTemp")

Do Until RecSet.EOF
    RecSet.Edit
        RecSet![tmpChangeType] = "Add"
        RecSet![tmpChangeDate] = Date
        RecSet![tmpImpactTicket] = Forms!frmNewEquip.varImpact.Value
 [COLOR="Red"]       RecSet![tmpTechnician] = strUsername[/COLOR]
    RecSet.Update
    RecSet.MoveNext
Loop
    RecSet.Close
    Set RecSet = Nothing
    
    Call CreateSpreadsheet
End Sub

Thank you much,

Rick

 

[RickRo]

..I have changed things around and tried so many different ways to do this, I didn't notice I had the wrong string variable in the above code - I have tried it with the correct name:
RecSet![tmpTechnician] = strTechName

 

[pbaldy]

And did that resolve the problem? An update query would be more efficient than the loop by the way.

 

[RickRo]

No that didnt fix the problem. I'm using a record set because I am adding some data that isnt in the form, but I want it in the the temp table because Im writing this to a and excel templete as well.

 

[pbaldy]

Well, everything in that code could be done by a query. In any case, what's the error? Have you set a breakpoint and checked values?

 

[RickRo]

The error I get is a Run-time Error '3421'; Data type conversion error.
In the line before this I put in a Debug.Print (strTechName) and I get the correct data in my immediate window.

I have tried to do this with an insert query, but I couldn't get it to work, and during my research I thought it was recommended to use a record set.

My goal is to get it to work and learn more along the way. If its better to do with an insert query, I will start over again in that direction.

Thank you so much for your assistance.

 

[gemma-the-husky]

without getting into the detail of using a recordset, as opposed to a differnet method, try these changes in blue

[COLOR=navy][B]option explicit[/B][/COLOR]
[COLOR=navy]'causes variables to be checked, to avoid inadvertent errors[/COLOR]
 
Public Sub WritetblTest()
Dim RecSet As Recordset
[B][COLOR=navy]dim db as database[/COLOR][/B]
Dim strTechName As String
 
strTechName = ReturnUserName()
[B][COLOR=navy][/COLOR][/B] 
[COLOR=#000080]'often helps to declare db as a separate object.[/COLOR]
[B][COLOR=navy]set db=currentdb[/COLOR][/B]
Set RecSet = [COLOR=navy][B]Db[/B][/COLOR].OpenRecordset("tblTemp")
 
Do Until RecSet.EOF
RecSet.Edit
RecSet![tmpChangeType] = "Add"
RecSet![tmpChangeDate] = Date
RecSet![tmpImpactTicket] = Forms!frmNewEquip.varImpact.Value
RecSet![tmpTechnician] = [COLOR=navy][B]strUsername[/B][/COLOR]
[COLOR=navy]'re above - you are using strUserName, but elsewhere you used strTechName - this may be an issue, wihch is I why i mentioned the option explicit[/COLOR]
 
RecSet.Update
RecSet.MoveNext
Loop
RecSet.Close
Set RecSet = Nothing
 
Call CreateSpreadsheet
End Sub

out of interest, assuming you want to just save the current windows username you could just do

RecSet![tmpTechnician] = environ("username")

 

[RickRo]

I tried setting db as and object, and that didn't work; I also tried adding:

RecSet![tmpTechnician] = environ("username")

That didn't work either; I still get:

"Run-time Error '3421'; Data type conversion error."

I also tried "RecSet![tmpTechnician] = ReturnUserName()" and that got the same error.

I can't understand why I can't get a variable into my table.

I cleaned up the code:

Private Sub butSavUpdate_Click()

Dim RecSet As Recordset
Dim strTechName As String
Dim db As Database

Set db = CurrentDb
Set RecSet = CurrentDb.OpenRecordset("tblTemp")

strTechName = ReturnUserName()

Do Until RecSet.EOF

    RecSet.Edit
    
        RecSet![tmpChangeType] = "Add"
        RecSet![tmpChangeDate] = Date
        RecSet![tmpImpactTicket] = varImpact.Value
                    
        RecSet![tmpAssetClass] = cboAssetClass.Value
        RecSet![tmpManufacturerName] = cboManufactuer.Value
        RecSet![tmpAssetSubCat] = cboAssetSubClass.Value
        RecSet![tmpMake] = cboModel.Value
        RecSet![tmpStatus] = "Stock"
            
        RecSet![tmpFromBuilding] = "LEASING CO"
        RecSet![tmpFromFloor] = "1"
        RecSet![tmpFromRoom] = "1"
        RecSet![tmpFromStreet] = "CSI Leasing"
        RecSet![tmpFromCity] = "FOND DU LAC"
        RecSet![tmpFromState] = "WI"
        RecSet![tmpFromZip] = "54935"
        RecSet![tmpFromCountry] = "US"
            
        RecSet![tmpToBuilding] = "SAH - WEST WING"
        RecSet![tmpToFloor] = "7"
        RecSet![tmpToRoom] = "1"
        RecSet![tmpToStreet] = "430 EAST DIVISION STREET"
        RecSet![tmpToCity] = "Fond du Lac"
        RecSet![tmpToState] = "WI"
        RecSet![tmpToZip] = "54935"
        RecSet![tmpToCountry] = "US"
            
 Debug.Print (strTechName)
 
       RecSet![tmpTechnician] = strTechName
       
    RecSet.Update
    RecSet.MoveNext

Loop

    RecSet.Close

Set RecSet = Nothing

Call CreateSpreadsheet

DoCmd.Close acForm, Me.Name

End Sub

 

[pbaldy]

What is the data type of tmpTechnician in the table? I wonder if it's expecting a numeric ID rather than a name.

 

[RickRo]

Oh my goodness, that was it!!! I had the darn thing as an integer. Thank you so much pbaldy!!!

 

[pbaldy]

Happy to help!