How do I write Table fields in code & Macro

[Guest]

I have managed to open and goto a new record in both Macro & code but the
setvalue to enter a value dosn't work for me. I get a message that it cannot
find the the entry and I need an identifier like [Forms]! etc. The builder
supplied my entry which was just the field name [DocName] and I have tried
the table name as well. [Saved Properties][Doc Name].
The help says that setvalue is not used in VB. I do not know how to add a
value to a Table in vb. I hope someone can help.

 

[Allen Browne]

This example shows how to open a recordset into the [Saved Properties] table
that retrieves just the fields the match the record you are trying to write.
It creates it if it does not exist, and changes the value if it does.

Function SaveProp(strDoc As String, strField As String, strProp As String,
varValue As Variant)
Dim rs As DAO.Recordset
Dim strSql As String

If (strDoc <> vbNullString) And (strProp <> vbNullstring) _
And Not IsNull(varValue) Then
strSql = "SELECT [Saved Properties].* FROM [Saved Properties] " & _
"WHERE ([DocName] = """ & strDoc & """)" & IIf(strField <>
vbNullString, _
" AND ([FldName] = """ & strField & """)", vbNullString) & _
" AND ([PrpName] = """ & strProp & """);"

Set rs = dbEngine(0)(0).OpenRecordset(strSql)
If rs.RecordCount = 0 Then
rs.AddNew
rs![DocName] = strDoc
If strField <> vbNullString Then
rs![FldName] = strField
End If
rs![PrpName] = strProp
rs![PrpValue] = varValue
rs.Update
Else
rs.Edit
rs![PrpValue] = varValue
rs.Update
End If
rs.Close
End If
Set rs = Nothing
End Function

 

[Guest]

Thanks I just added a reference to DAO and it worked like a dream.
Please answer 1 more thing.
Surely if you can open, search and close a table in both macro and VB
without using DAO then you would think you can modify and add in the same
way.
The access help says that SetValue works on Tables and in VB you must access
it directly !!
Is there a method or must DAO be used. (I don't like making SQL statements
out of unknown string variables. It seems to complex for an easy life.)
Thank you again

 

[Allen Browne]

You cannot use SetValue directly on a table.

It is possible without DAO, using action queries. But this would be much
less efficient, requiring 3 steps:

1. Discover if the record already exists in the table. This would need
something like a DLookup().

2. If found, create an Update query string, and execute the string with
RunSQL to set it to the desired value.

3. If not found, create an Append query string, and execute it with RunSQL.

The DAO approach has the advantage of:
a) performing all these at once,
b) knowing the the write succeeded or not (which RunSQL cannot.)
c) making it easy to adapt for writing multiple properties.

It is worth investing a couple of hours to learn how to do this, as it will
open up an entire world of possibilities for you. The sample code should get
you started. Then play around with it until you understand what it is doing
and why.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks I just added a reference to DAO and it worked like a dream.
Please answer 1 more thing.
Surely if you can open, search and close a table in both macro and VB
without using DAO then you would think you can modify and add in the same
way.
The access help says that SetValue works on Tables and in VB you must
access
it directly !!
Is there a method or must DAO be used. (I don't like making SQL statements
out of unknown string variables. It seems to complex for an easy life.)
Thank you again

I have managed to open and goto a new record in both Macro & code but the
setvalue to enter a value dosn't work for me. I get a message that it
cannot
find the the entry and I need an identifier like [Forms]! etc. The
builder
supplied my entry which was just the field name [DocName] and I have
tried
the table name as well. [Saved Properties][Doc Name].
The help says that setvalue is not used in VB. I do not know how to add a
value to a Table in vb. I hope someone can help.