Simple, I hope

[Bill]

From time-to-time I find it useful to use
DLookup to obtain a value from one of
a table's fields. Is there there the reverse
of that? I.e., save a value into a table
field with a criteria of the record's ID?

I looked in SQL, but I have the idea
I missed something.

Bill

 

[Steve Schapel]

Bill,

I am not 100% clear what you mean. Maybe a specific example would help.
But it could be that an Update Query is what you are looking for?
UPDATE YourTable SET YourField = "something" WHERE ID = x

 

[Dale Fye]

There is no canned function like DLOOKUP, but you could use:

Public Function fnPut(Fieldname as string, Tablename as string, _
SomeValue as Variant, Optional Criteria as
Variant = NULL) as boolean

Dim strSQL as string
Dim rs as DAO.Recordset

On Error GoTo fnPutError

strSQL = "SELECT [" & Fieldname & "] " _
& "FROM [" & Tablename & "] " _
& ("WHERE " + Criteria)
set rs = Currentdb.openrecordset (strsql, , dbfailonerror)

While not rs.eof
rs.edit
rs(Fieldname) = SomeValue
rs.update
rs.movenext
Wend
fnPut = true

fnPutExit:
if not rs is nothing then
rs.close
set rs = nothing
endif
Exit Function
fnPutError:
msgbox err.number & err.description, vbInformation + vbOkOnly, "Error:
fnPut"
fnPut = false
Resume fnPutExit
End sub

I use a function that looks like this, although I don't have access to it at
the moment (this is air code, so you might run into a bug or two).

While this is slower than an UPDATE statement especially if the criteria
returns multiple values, you avoid having to deal with making sure you have
your SomeValue parameter in the right format. This will return a true if
the update worked and a false if an error was generated.

HTH
Dale

 

[Bill]

An Update Query provides the functionality I'm looking
for. So, if I build the SQL string, what command syntax
do I use to reference the current back-end database?

Bill

 

[Dale Fye]

If the tables in the BE db are linked to your front end. you would use the
same syntax mentioned by Steve in his earlier post.

The thing I like about my function is that you don't have to develop error
code for each of your modules where you write the UPDATE queries. You only
have to have one set of error handling, and you do need to include error
handling. What I put in my fnPut( ) function is the minimal error handling
I use during development, but I generally have a more robust error handling
messages.

HTH
Dale

 

[Bill]

I didn't ask the question correctly, or at least not clearly.
Steve gave me the SQL statement, which I understand,
but since I don't know the path of the backend mdb, I
wanted to use some form of an execute command where
I simply reference "currentdb", of somethink like that.
Bill

 

[Steve Schapel]

Bill,

I'm still not really understanding the question, I'm afraid. As per
Dale's reply, you don't need anything else. You are referring to it as
the "backend mdb", right? To me that implies that the table you are
trying to update is in the backend mdb, and that you have a link to that
table from the frontend application file. Right? And you are working
in the frontend application file, right? So you just make an Update
Query within the frontend, using the linked backend table. So it's just
like I said before:
UPDATE YourTable SET YourField = "something" WHERE ID = x
You don't need anything else. Either that, or you are going to need to
provide some more specific and detailed example of what you are trying
to do.

 

[Bill]

Steve,
The problem lies solely with me. With the statement:

UPDATE Setup SET ImageRatio = 1.888 WHERE RatioID = 1

I applied the SQL mindset to something that apparently is much
simpler. I thought that what you were telling me was something
more like

Dim strSQL as String
strSQL = "UPDATE Setup SET ImageRatio = ""1.888"" WHERE RatioID = 1"

Execute.something strSQL

and, my follow-up posts were asking what that "something" was that
went with the Execute when I don't know the path of the backend.

Anyway, before this post, I attempted to code the UPDATE statement
as shown above:

UPDATE Setup SET ImageRatio = 1.888 WHERE RatioID = 1

and the compiler is having fits because it is expecting "End of Statement"

Bill

 

[John W. Vinson]

Steve,
The problem lies solely with me. With the statement:

UPDATE Setup SET ImageRatio = 1.888 WHERE RatioID = 1

I applied the SQL mindset to something that apparently is much
simpler. I thought that what you were telling me was something
more like

Dim strSQL as String
strSQL = "UPDATE Setup SET ImageRatio = ""1.888"" WHERE RatioID = 1"

Execute.something strSQL

and, my follow-up posts were asking what that "something" was that
went with the Execute when I don't know the path of the backend.

Anyway, before this post, I attempted to code the UPDATE statement
as shown above:

UPDATE Setup SET ImageRatio = 1.888 WHERE RatioID = 1

and the compiler is having fits because it is expecting "End of Statement"

VBA is one language, written in Modules.

SQL is a different language, written in queries.

If you create a new query, open the SQL window, and paste in

UPDATE Setup SET ImageRatio = 1.888 WHERE RatioID = 1

as the entirity of the query, and execute it, you will get the desired result.

Your problem is that you're pasting SQL query commands into a VBA module,
which ist als ich in Deutsch wechselt mittens ins Antwort.

 

[Steve Schapel]

Bill,

I agree entirely with John's advice.

However, if you *do* have a requirement to run this within a VBA
procedure, you can still do that. Very similar to what you already
suggested:

Dim strSQL as String
strSQL = "UPDATE Setup SET ImageRatio = 1.888 WHERE RatioID = 1"
CurrentDb.Execute strSQL

You see, it is still the current database that is executing the action
query. It is applying the Update to a linked table. Once again, the
whereabouts of that linked table is not something you need to worry about.

 

[Bill]

Okay Steve, I got it.

I have to laugh though, at how convoluted I managed
to get this thread. I managed to get Vinson thinking I
didn't know the difference between VBA and SQL
languages. It certainly would have helped if I had
responded more completely to your initial reply, in
that the answer is "no", there's isn't a VBA function
to store a value into the field of a table. Rather, one
uses a SQL UPDATE query to accomplish that
task. That led to my follow-on question of the VBA
statement used to interface to SQL when I don't
know the path of the backend, which you've answered
here.

Thanks again.
Bill

 

[Bill]

Thanks Dale. I'll put your code segment into my
box of utilities and study the error handling code
for later use.
Bill

There is no canned function like DLOOKUP, but you could use:

Public Function fnPut(Fieldname as string, Tablename as string, _
SomeValue as Variant, Optional Criteria as
Variant = NULL) as boolean

Dim strSQL as string
Dim rs as DAO.Recordset

On Error GoTo fnPutError

strSQL = "SELECT [" & Fieldname & "] " _
& "FROM [" & Tablename & "] " _
& ("WHERE " + Criteria)
set rs = Currentdb.openrecordset (strsql, , dbfailonerror)

While not rs.eof
rs.edit
rs(Fieldname) = SomeValue
rs.update
rs.movenext
Wend
fnPut = true

fnPutExit:
if not rs is nothing then
rs.close
set rs = nothing
endif
Exit Function
fnPutError:
msgbox err.number & err.description, vbInformation + vbOkOnly, "Error:
fnPut"
fnPut = false
Resume fnPutExit
End sub

I use a function that looks like this, although I don't have access to it
at the moment (this is air code, so you might run into a bug or two).

While this is slower than an UPDATE statement especially if the criteria
returns multiple values, you avoid having to deal with making sure you
have your SomeValue parameter in the right format. This will return a
true if the update worked and a false if an error was generated.

HTH
Dale

From time-to-time I find it useful to use
DLookup to obtain a value from one of
a table's fields. Is there there the reverse
of that? I.e., save a value into a table
field with a criteria of the record's ID?

I looked in SQL, but I have the idea
I missed something.

Bill