still having problem in moving record to other table

[hu5]

Hi, Thank you for your kind help, the timestamp works great, but the 2nd question still having problem. I meant to move the completed record to other table, so the record also need to be deleted from the original file. Thanks again.

1. If "Text18" text string contains more than just "COMPLETED”, it won’t get transferred to archive table, seems that it looks for "COMPLETED" only. It should be moved to archive table as long as COMPLETED is typed in.
How to concatenate for longer text string? I thought InStr will do the job.

2. the record fields is up to 50 fields, is there a simple way to include all fields instead of concatenate every fields?

3. is there a move command for the whole record instead of insert into & delete statement? If not, do I simply add delete statement like you did for insert into statement? For example:

dim sSql as string

sSql = "INSERT INTO table2 ( fldDate, fldNum, fldText ) " & _ "VALUES (#" & Forms![MyForm]!txtDate & "#," & Forms![MyForm]!txtNum & _ ",'" & Forms![MyForm]!txtText & "');"

sSql = "delete from table1 ( fldDate, fldNum, fldText ) " & _ " (#" & Forms![MyForm]!txtDate & "#," & Forms![MyForm]!txtNum & _ ",'" & Forms![MyForm]!txtText & "');"

docmd.runsql ssql

I’m having some problem with the following code
____________________________________________________________

Option Compare Database

Private Sub Text18_AfterUpdate()
Dim i As Integer

Dim sSql As String


For i = 13 To 50
If IsNull(Me("field" & i).Value) Then
Me!Text18.Value = Me!Text18.Value & " " & Now

Me("field" & i).Value = Me!Text18.Value
Exit For
End If
Next i


If InStr(Me!Text18.Value, "completed") = 1 Then

sSql = "INSERT INTO Completed_FY04 (StartDate, Division, SRMNo, Status) " & _
"VALUES (#" & Forms![certStatus]!StartDate & "#," & Forms![certStatus]!Division & _
",'" & Forms![certStatus]!SRMNo & "'" & _
",'" & Forms![certStatus]!Status & "');"
DoCmd.RunSQL sSql
End If


End Sub
____________________________________________________
RoyVidar (Instructor) Apr 27, 2004
Hi!

#1Adding a timestamp to a control:Me!txtControl.Value = NowShould it alredy contain something, you could try concatination:Me!txtControl.Value = Me!txtControl.Value & " Tmstmp: " & NowEvent - hmmm - if it's every time a change is saved, use the before update event of the form (fires whenever a save is performed)

#2Perhaps the before update/after update event of the form, run some sql:

dim sSql as string
sSql = "INSERT INTO table2 ( fldDate, fldNum, fldText ) " & _ "VALUES (#" & Forms![MyForm]!txtDate & "#," & Forms![MyForm]!txtNum & _ ",'" & Forms![MyForm]!txtText & "');"
docmd.runsql ssql

Is the code supposed to test for the text "completed" within the textbox?

if instr(me!txtBox.Value,"completed")=0 then ' perform the above... -

this sample contains both date, numeric, and text fields, note the delimiters, hash (#) for dates, single quote (') for text, nothing for numerics. - but saving the same data in two tables, isn't encouraged (violates lots of database rules ), it's bound to give future headaches...

 

[RoyVidar]

The InStr does that, I just goofed on the operator (should be possible to spot in some testing, or pressing F1 on InStr in VBE)

InStr returns 0 if no match is found, else the starting position of the text, so:

[tt]If instr(me!txtBox.Value,"completed")>0 then ' perform the above... -[/tt]

would probably be more correct.

If you are going to pick up the values from the form controls, and put into another table, then you'll have to specify each field and control.

Another version would be for instance something like this, using the PK (or any unique identifier) of the controlsource of the form. First catching the unique value, then doing some sql (here using a numeric):

[blue]

  dim sSql as string
  dim lPK as long
  lPK=Me!txtPKControl.Value
  ssql="insert into table2 " & _
      "select * from table1 " & _
      "where PKField = " & lPK
  docmd.setwarnings false
  docmd.runsql ssql
' Then delete the current record, there are 
' several methods, but here using sql
  ssql="delete * from table1 " & _
      "where PKField = " & lPK
  docmd.runsql ssql
  docmd.setwarnings true
  me.requery

[/blue]
- where table1 is the recordsource of the form, table2 is the new table
- typed not tested

If you're having difficulties with some particular part of code, please refer to it (the line(s)), and also state which errormsg you're getting, what it does/does not/should have done...

See also you're a relative new member, welcome to Tek-Tips, and here's a little faq on how to get the most out of the membership faq181-2886.

Roy-Vidar

 

[legos]

here's a few suggestions
for #1 there is an easy way to select text that has more than just completed using wild card characters.
If InStr(Me!Text18.Value, '*"completed"*') = 1 Then
i'm not 100% on the format using completed in that example but something like this would certainly work
dim check as string
check = "completed"
If InStr(Me!Text18.Value, '*" & check & "*') = 1 Then
That should work. It also comes in handy when checking record sets or using the dcout function such as this example below.

off = DCount("API", "Velocityoffshore", "API Like '*" & Me.lstvel.Column(0, row) & "*'")

Set rst = db.OpenRecordset("SELECT * FROM VelocityLAOnshore WHERE API Like '*" & Me.lstvel.Column(0, row) & "*'")
#2 as far as i know the answer is no. But on the plus side there is an easy way to delete an entire record, which it seems you have already found.
This is my general delete statement, it might be a little more clean than what you have, in it's current state it deletes all the records which are displayed in a list box on a form from a table

Dim SQL As String
Dim intitems As Integer
For intitems = 0 To Me.StoredQueries.ListCount - 1
DoCmd.SetWarnings False
SQL = "Delete * From Stored"
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
Next intitems
The delete statement can also be modified to fit certain criteria much like the dcount and rst functions above.
SQL = "Delete * From LogInventory WHERE FileName ='" & FileNameT & "'"
DoCmd.RunSQL SQL
FileNameT is a textbox contained on a table, if you wanted to delete all records that had a field with that criteria you could use a dcount to find the number of matches and then use a for loop with the sql statement.
Lastly i never use the insert statement to move records, usually i will make a recordset and either add or edit records in the new table.
Here is a good example of this that can be changed to suite your needs, you can just change the listbox querymatches to your textbox.
For intitems = 0 To Me.QueryMatches.ListCount - 1
Set rst1 = db.OpenRecordset("SELECT * FROM LogInventory WHERE FileName='" & Me.QueryMatches.Column(10, intitems) & "'")
Set rst2 = db.OpenRecordset("SELECT * FROM Stored")
With rst2
.Addnew
!Section = rst1!Section
!township = rst1!township
!range = rst1!range
!countyparish = rst1!countyparish
!Field = rst1!Field
!well = rst1!well
!wellnumber = rst1!wellnumber
!company = rst1!company
!state = rst1!state
!depth = rst1!depth
!filename = rst1!filename
!apinumber = rst1!apinumber
!serialnumber = rst1!serialnumber
.Update
End With
rst1.MoveNext
Next intitems

Hope that all helps!

Durible Outer Casing to Prevent Fall-Apart