Full Version: Modify a record on another table
UtterAccess Forums > Microsoft® Access > Access Forms
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 & "'")
Tlookup("[IsTerm]", "Products", "DRCProdCode = '" & Forms!InvoiceGen!ODetailCompany.Form.Controls!DRCProdCode & "'") = IsTerm
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.
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:

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
        rst!IsTerm = IsTerm 'Make your change
        rst.Update 'Updates the field
    End If
    Set rst = Nothing  [color="green"] 'Cleanup [/color]
End Sub
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
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!
Or if you want to move away from DAO, you can use ADO...
urrentProject.Connection.Execute strSQL
You mean CurrentDb.Execute doesn't work without setting a reference to DAO?
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.
Thanks the clarification, Cheek. Good explanation.
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.
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
I'm glad it was helpful.
ummm..... Help? It bombs on this line:
rst.Find "[RadioSerial]= '" & Forms!RentalGen!RentalDetails.Form.Controls!RadioSerial & "'"""

I also tried this: (It's a text string)
st.Find "[RadioSerial]= " & Forms!RentalGen!RentalDetails.Form.Controls!RadioSerial
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.
I got it!!!
Here's what I had to do:
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
        rst!IsOut = True
        Me.IsOut = True
    End If
    Set rst = Nothing

Thanks, Guys for all your help!
You guys ROCK.
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?
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.