REPLACE DATA IN A COLUMN WITH A PARAMETER 1

[DIVINEDAR0956]

I have searched everything I can possibly search and I don't really know how to explain this.

I would like to change the data or replace the data in a column with other data. I know I can use edit and paste but what I would like to do is have a code to attached to a button that when clicked it will ask you to put in the number you want to replace with the information you want to replace it with. Such as Replace P0026 with P0053. Say I have 4 tables and this information is all 4 tables in 1 column with the same field heading such as Project Number. Project Number being the key field.

Please help.

 

[FancyPrairie]

Try something like this (Assume your table names are "Table1", "Table2", "Table3", and "Table4&quot

    Dim rst as ADODB.Recordset
    Dim i as integer

    for i = 1 to 4
        rst.open "Select [Project Number] from Table" & i & ",CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        rst![Project Number] = "Your New Value"
        rst.Update
        rst.close
     next i

 

[DIVINEDAR0956]

Thank you for your assistant FancyPrairie. Question for you. Don't I have to define the tables regarding the ADOBE.Recordset and if so how do I do that. When I debug it stops there saying about not being defined. Do I define 1 and such table, 2 as such table and so on. Thank you. It looks like it will work but I need a push on defining.

 

[Tofias1]

Divinedar0956,

You need to go to the Tools option within Mircrosoft VBA code builder. Choose References then scroll down the list and choose Microsoft DAO 3.6 Object Library. This should work. Also check on Help within the code builder and type in recordset and it will explain to you why you need to check it off to define it and so on.....

Hope this helps.

Tofias1

 

[DIVINEDAR0956]

You guys are totally awesome.

 

[DIVINEDAR0956]

Okay guys what am I doing wrong. I have the following that was sent to me:

Public Sub CopyNewProject()
Dim rst As ADODB.Recordset
Set rst = Recordset
Dim i As Integer


For i = 1 To 4
rst.Open "Select [JOBNUMBER] from Table" & i & ",CurrentProject.Connection, adOpenDynamic, adLockOptimistic"
rst![JobNumber] = "Your New Value"
rst.Update
rst.Close
Next i
End Sub

When I get here "Set rst = Recordset" I get an error message saying "OBJECT REQUIRED". How do I set an object to a recordset? Also How do I write this where it will do all four tables?

Please help.

 

[FancyPrairie]

1st, the line of code should look like this (I forgot to add it in my original post):
Set rst = New ADODB.Recordset

2nd, the loop your doing (for i = 1 to 4) is for opening each table. The code in my original post assummed that there was some kind of naming convention for your tables (i.e. Table1, Table2, etc). Based on this assumption, the line of code that contains from Table" & i & would produce this result from Table1. However, if you don't have a naming convention, then your code should look like this:

Public Sub CopyNewProject()
Dim rst As ADODB.Recordset
Dim i As Integer
Dim strTblName as string

Set rst = New ADODB.Recordset

For i = 1 To 4
strTblName = Choose(i,"NameOfFirstTable","2ndTbl","3rdTbl","4thTbl&quot
rst.Open "Select [JOBNUMBER] from " & strTblName & ";" ,CurrentProject.Connection, adOpenDynamic, adLockOptimistic"
rst![JobNumber] = "Your New Value"
rst.Update
rst.Close
Next i
End Sub

 

[DIVINEDAR0956]

FancyPrairie thank you very much. It works perfectly. There's only one more thing. Where it says "Your New Value" I would like for that to be a parameter that changes it to whatever Project Number it is entered into it. How do I get it to prompt for a new project number.

Again you guys are awesome.

thank you.

 

[FancyPrairie]

If you want it to prompt thru each iteration of the for loop, then use the InputBox function

Public Sub CopyNewProject()
Dim rst As ADODB.Recordset
Dim i As Integer
Dim strTblName as string
dim strNewValue as string

Set rst = New ADODB.Recordset

For i = 1 To 4
strNewValue = InputBox("Enter Project Number&quot
strTblName = Choose(i,"NameOfFirstTable","2ndTbl","3rdTbl","4thTbl&quot
rst.Open "Select [JOBNUMBER] from " & strTblName & ";" ,CurrentProject.Connection, adOpenDynamic, adLockOptimistic"
rst![JobNumber] = strNewValue
rst.Update
rst.Close
Next i
End Sub

If you use the inputbox function, you're going to have to check to ensure the value the enter is valid, etc.

 

[DIVINEDAR0956]

FancyPrairie thank you that works good but it doesn't change the whole recordset it only changes one record. Also is there a way for it to ask for the prompt once and change all the records in all the tables. I hope I'm not asking a lot but I need help and this is my last leg to complete my program. Thank you for your help and your patience.

Darlene

 

[FancyPrairie]

I'm not sure I understand what you are asking. Are you saying that you want to change the value of the first field in all of the records of each table? If so, here's how you do it.

Public Sub CopyNewProject()
Dim rst As ADODB.Recordset
Dim i As Integer
Dim strTblName as string
dim strNewValue as string

Set rst = New ADODB.Recordset

strNewValue = InputBox("Enter Project Number&quot

For i = 1 To 4

strTblName = Choose(i,"NameOfFirstTable","2ndTbl","3rdTbl","4thTbl&quot
rst.Open "Select [JOBNUMBER] from " & strTblName & ";" ,CurrentProject.Connection, adOpenDynamic, adLockOptimistic"
while not rst.eof
rst![JobNumber] = strNewValue
rst.Update
rst.MoveNext
wend
rst.Close
Next i
End Sub

 

[DIVINEDAR0956]

thank you it worked. I tell you, you guys are totally awesome. Now another question. When it prompts for change of Project Number I would like for it to prompt just once and change all tables. Is that possible.

Thank you for your help.

 

[FancyPrairie]

The last set of code will only prompt you once, not several times. Note where the Inputbox code is located. It is ABOVE the For statement.

 

[DIVINEDAR0956]

I give you a star for help and support. Keep up the good work and you might be hearing from me again soon.

Again thank you and it works perfectly.