Multiple pastes

[sds]

Hi all - newbie here - tried to find the answer to this question but
failed - hope someone here can help. It should be a simple question so maybe
I'm making life harder than it needs to be.

I use MS Access 2002 - and have numerous tables that I have to paste in the
same text string. How can I do it so I don't have to paste 200 times - is
there a way to copy my text, highlight the cells I want to paste it into,
and then have my text string appear in all 2000 cells? Is there a way to do
this?

Thanks.

Simon

[pietlinden]

Sounds like a dodgy database design. I can't think of an instance in
which I've needed to do this. If you're doing it across a definable
set of records in a single table, use an update query. What you're
describing sounds like you're coming from the world of Excel, and
Access and Excel are radically different.

So describe your database tables - just the ones you want to paste this
string into, and the overall design. (no need for lots of details -
keep it short and sweet.)

[simon]

Hey - thanks.

The db has multiple tables, each with a list of items, ID #'s, description
and an image location cell...........this is basically the basis for a drill
down db to be put on the web. I want to have the image location cell be the
same default image location until such time as an item specific image is
uploaded.

Thanks again!

Simon
<pi********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Sounds like a dodgy database design. I can't think of an instance in
which I've needed to do this. If you're doing it across a definable
set of records in a single table, use an update query. What you're
describing sounds like you're coming from the world of Excel, and
Access and Excel are radically different.

So describe your database tables - just the ones you want to paste this
string into, and the overall design. (no need for lots of details -
keep it short and sweet.)

[downwitch]

You can try this, it worked once upon a time ;)... Be sure to (1) watch
out for spurious line breaks, (2) set a reference to the DAO library,
and (3) pay attention to that warning in the update-writer about value
types in the fields. (I'd have added that code too, but it was getting
out of hand as it was.)

Oh and it won't help much if you have fifty different field names, but
since you spoke of drill-downs it should get you started at least. You
can loop through all fld in rst.Fields almost as easily. It won't be
fast.

'*** CODE START ***
Public Function ReplaceFldValueInAllTbls(ByVal pstrFldNm As String, _
ByVal pvarValueToFind As
Variant, _
ByVal pvarReplacement As
Variant) As String
'On Error GoTo HandleErrors

Dim obj As AccessObject
Dim dbs As Object

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim qdf As DAO.QueryDef

Dim strSQL As String
Dim strResult As String

Dim strLkupTbl As String
Dim strFldsLst As String
Dim bleUpdateFld As Boolean
Dim strUpdateTblFld As String

Dim strMsg As String
Dim varResponse As Variant

Dim varSysCmd As Variant
Dim intCount As Integer
Dim i As Long

Set dbs = Application.CurrentData

Set db = CurrentDb

For Each obj In dbs.AllTables
varSysCmd = SysCmd(acSysCmdInitMeter, "searching tables for " &
pstrFldNm & "...", dbs.AllTables.Count)

bleUpdateFld = False
strUpdateTblFld = ""
Set rst = db.OpenRecordset(obj.Name, dbOpenSnapshot,
dbForwardOnly)

For Each fld In rst.Fields
If fld.Name = pstrFldNm Then
strUpdateTblFld = obj.Name & "." & fld.Name
If InStr(1, strFldsLst, strUpdateTblFld) = 0 Then
bleUpdateFld = True
Else
bleUpdateFld = False
End If
ElseIf InStr(1, fld.Name, pstrFldNm, vbBinaryCompare) > 0
Then
strUpdateTblFld = obj.Name & "." & fld.Name
If InStr(1, strFldsLst, strUpdateTblFld) = 0 Then
strMsg = "Replace values in " & strUpdateTblFld &
"?"
varResponse = MsgBox(strMsg, vbYesNo Or vbQuestion,
"partial field-name match")
bleUpdateFld = (varResponse = vbYes)
Else
bleUpdateFld = False
End If
Else
bleUpdateFld = False
End If

If bleUpdateFld = True Then
strFldsLst = strFldsLst & ", " & strUpdateTblFld
'write an
strSQL = usqlWriteSimpleUpdate(obj.Name, fld.Name,
pvarValueToFind, pvarReplacement)
Set qdf = db.CreateQueryDef("", strSQL)

qdf.Execute
i = qdf.RecordsAffected
If i > 0 Then
strMsg = strUpdateTblFld & ": " & i & " record(s)
updated." & strMsg
strResult = strResult & vbNewLine & strMsg
Else
strResult = strResult & vbNewLine & strUpdateTblFld
& " - no matches found"
End If
End If
Next fld

intCount = intCount + 1
varSysCmd = SysCmd(acSysCmdUpdateMeter, intCount)
Next obj

ReplaceFldValueInAllTbls = strResult

ExitHere:
Set qdf = Nothing
Set fld = Nothing
Set obj = Nothing
Set db = Nothing
Set dbs = Nothing
varSysCmd = SysCmd(acSysCmdRemoveMeter)

Exit Function

HandleErrors:
'do it
Resume ExitHere

End Function

Public Function usqlWriteSimpleUpdate(pstrTblQryNm As String, _
pstrFldNm As String, _
pvarValueToFind As Variant, _
pvarReplacementValue As Variant)
As String
On Error GoTo HandleErrors

Dim strUpdateTblFld As String

strUpdateTblFld = pstrTblQryNm & "." & pstrFldNm

'CAUTION: this assumes numerical pvarValueToFind !!!!!
'i.e. strings need "", dates need ## and in the right order, etc.,
to make valid SQL

usqlWriteSimpleUpdate = "UPDATE " & pstrTblQryNm & " SET " &
strUpdateTblFld & " = " & pvarReplacementValue & vbNewLine _
& "WHERE (" & strUpdateTblFld & " = " &
pvarValueToFind & ");"
ExitHere:
Exit Function

HandleErrors:
'do it
Resume ExitHere

End Function
'*** CODE END ***

[pietlinden]

FWIW, it sounds like you can simplify this a lot. Just add a field to
differentiate the various records (like having the group that the item
belongs to). Then you could have one table and an infinitely simpler
and more flexible design.

[Thelma Lubkin]

simon <sd*@sdc.com> wrote:

: The db has multiple tables, each with a list of items, ID #'s, description
: and an image location cell.....this is basically the basis for a drill
: down db to be put on the web. I want to have the image location cell be the
: same default image location until such time as an item specific image is
: uploaded.
Why don't you just leave the image location cell empty and have
code that uses the contents of the image location cell if filled,
and a preset variable containing the default if it's empty?
--thelma

: Simon