Find a record, and creating a new record.

[Zooropa]

Complete Access newbie, here.

I have a table with 2600 entries on it.
Each record has a unique number.

I created a form, based on this table.

I have a search button on the form, and when I enter the unique number, hit search, it brings me back the correct record.

That's fine, it took me a wee while, but I worked out using a macro saying:

GotoControl -> Unique Number
FindRecord >- =[searchbox]

..pulls back the correct record.


Now, I'd like to add a button to the form, which takes the record that's been found, and copy it to a new table.

I'm pretty sure there's a fairly easy solution, but I'm completely stuck :S

Any help would be appreciated.

Thanks.

Marc.

 

[Ukraine82]

http://www.access-programmers.co.uk/forums/showthread.php?t=34732&highlight=duplicate

hth,

Michael

 

[Zooropa]

Excellent, thanks very much.

 

[Zooropa]

I've had a look, and it all seems fine for copying a record to the same table, but nothing seems to be mentioned about copying it to a different table.

 

[Zooropa]

Got it.
Just got the button to run an SQL query, using the value in the text box as a variable.

Private Sub Command14_Click()
Dim Pimsno As String
Dim strSQL As String

Pimsno = Me.Text10.Value
'MsgBox "" & Pimsno & ""
  
strSQL = "INSERT INTO [New Tracking Table] ( [PIMS Identifier], [Planned Review Base], [Planned Review Date], [Planned Reviewer], [Text8] )" & _
         "SELECT [Planned Reviews].[PIMS Identifier], [Planned Reviews].[Planned Review Base], [Planned Reviews].[Planned Review Date], [Planned Reviews].[Planned Reviewer], [Forms]![testform]![Text8]" & _
         "FROM [Planned Reviews]" & _
         "WHERE ((([Planned Reviews].[PIMS Identifier])='" & Pimsno & "'));"

DoCmd.RunSQL strSQL


End Sub