Full Version: Modify a record on another table
UtterAccess Forums > Microsoft® Access > Access Forms
Ramdryve
heyas all.
I'm used to DLookup(), is there a similar function that will allow me to set a variable, or can I just turn around the dlookup, ie,
IsTerm = Tlookup("[IsTerm]", "Products", "DRCProdCode = '" & Forms!InvoiceGen!ODetailCompany.Form.Controls!DRCProdCode & "'")
to
Tlookup("[IsTerm]", "Products", "DRCProdCode = '" & Forms!InvoiceGen!ODetailCompany.Form.Controls!DRCProdCode & "'") = IsTerm
?????
TIA
Jack Cowley
I do not understand what it is you want to do. You can set a variable to a value that DLookup() finds as you did with IsTerm. From your post it appears that your want DLookup() to = IsTerm but that is not how it works. Give and example of what it is you want to do and someone here should be able to help you.
ack
Tomolena
If all you are trying to do is get to a particular row in your "Products" table and change the value in the IsTerm field, you can't do it that way. Recordsets aren't as hard to work with at it may seem. Here's some very generic code:
CODE

Private Sub ChangeTheValue(IsTerm)
Dim rst As ADODB.Recordset  [color="green"] 'Declare the recordset object  [/color]
    Set rst = New ADODB.Recordset  [color="green"]'Instantiate it  [/color]
    [color="green"] 'Very generic way to open the most flexible recordset: [/color]
    rst.Open "Products", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    rst.Find "[DRCProdCode]=" & Forms!InvoiceGen!ODetailCompany.Form.Controls!DRCProdCode
    If rst.EOF Then  [color="green"]'Test if the record exists  [/color]
        Set rst = Nothing
        Exit Sub
    Else
        rst!IsTerm = IsTerm 'Make your change
        rst.Update 'Updates the field
    End If
    Set rst = Nothing  [color="green"] 'Cleanup [/color]
End Sub
merlicky
Another possibility, depending on what you're actually trying to do, is to use an action query with the DoCmd.RunSQL function.
im sql As String
sql="UPDATE Products SET Products.IsTerm='" & IsTerm & _
"' WHERE Products.DRCProdCode='" & Forms!InvoiceGen!ODetailCompany.Form.Controls!DRCProdCode & "'"
DoCmd.RunSQL sql
Tomolena
Your right. I need to start changing my paridigm. I work so often with recordsets that it's often my first resort. One modification to your solution...
read one of the zen masters on this board commenting that RunSQL is not the best method for running an action query in code. I forget what exactly he said, but among the dificiencies, was the fact that you had to deal with SetWarnings. CurrentDb.Execute SQL is the preferred method these days...he said. I tested it. It works!
niesz
Or if you want to move away from DAO, you can use ADO...
urrentProject.Connection.Execute strSQL
Tomolena
You mean CurrentDb.Execute doesn't work without setting a reference to DAO?
cheekybuddha
Both
urrentDb.Execute strSQL
CurrentProject.Connection.Execute strSQL
will work even if you have neither a reference to DAO or ADO set.
However, if you want to use constants you must set the appropriate reference(s)
CurrentDb.Execute strSQL, dbFailOnError
CurrentProject.Connection.Execute strSQL, , adCmdText + adExecuteNoRecords
If you know the numeric value of the constants you can substitute them without setting references.
hth,
d
niesz
Thanks the clarification, Cheek. Good explanation.
cheekybuddha
Hi Walter,
It seems as if Access sets an internal reference to DAO/ADO when using CurrentDb/CurrentProject.Connection and it works as if you have late-bound objects.
d
Ramdryve
Tomolena:
That recordset example is exactly what I needed to learn how to use them! Thank you, ever so much!
for the rest of you, thank you all.
I am always amazed at the level of skill and knowledge that I share here, and I truly appreciate all the help all of you have given me on this project.
Thank you, all of you, for helping me. My boss seems impressed thus far, I just hope I can keep it up. <G>
Thanks again
Tomolena
I'm glad it was helpful.
Ramdryve
ummm..... Help? It bombs on this line:
rst.Find "[RadioSerial]= '" & Forms!RentalGen!RentalDetails.Form.Controls!RadioSerial & "'"""

TIA
Ramdryve
I also tried this: (It's a text string)
st.Find "[RadioSerial]= " & Forms!RentalGen!RentalDetails.Form.Controls!RadioSerial
Ramdryve
Here's where I am now, it now says "Object Required" and the debug points to the set sql line.
im serial As String
serial = Me.RadioSerial
Dim rst As ADODB.Recordset
Dim sql As String
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset
Set sql = "SELECT RentalProducts.* FROM RentalProducts WHERE RentalProducts.RadioSerial = '" & serial & "';"
rst.Open sql, conn, adOpenDynamic, adLockOptimistic
Tia, Folx.
Ramdryve
I got it!!!
Here's what I had to do:
CODE
Dim Serial As String
Serial = Me.RadioSerial
Dim db As Database, rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("RentalProducts", dbOpenDynaset)
rst.FindNext "Serial = '" & Serial & "'"
    
    If rst.EOF And rst.BOF Then
        Set rst = Nothing
        Exit Sub
    Else
        rst.Edit
        rst!IsOut = True
        Me.IsOut = True
        rst.Update
    End If
    
    Set rst = Nothing

Thanks, Guys for all your help!
You guys ROCK.
Tomolena
Sorry I left you hanging. I was seeking help with an indexing issue. Yes..You got it! For strings, you have to enclose the variable with hyphens, for dates, use hash marks (#); Everyting else is numbers so you don't need any extra symbols.

Just curious...Why did you switch to DAO?
Ramdryve
well, LOL, i was stuck, so I managed to find some code out there in the great google yonder that kind of matched what you showed me, then I managed to figure out, based on your explanation, what it all did, and kind of "put the two together" so to speak.
It works, and works well, so ahah!
It made me happy. frown.gif
Thanks for all your help, I wouldnt have been able to do this without you.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.