Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

form contents as a recordset

Status
Not open for further replies.

ninash

Technical User
Jul 6, 2001
163
GB
Hi All,

I have a form displaying information from 2 tables and a few custom fields that are acting as keys for unique record identification. I know I shouldn't do this but I want to write the information shown in the current form to a new table I have designed as a log of this record being generated. The form has been designed to only show the active dataset so there will be no other data to worry about.
How do I code a button to transfer the currenly displayed data on the form into my new table???

Can anyone help me???

Tony
 
make a query based on the tables and the field (all the fields u need)
Take a where clause for the record (look for ID normally)
and strSQL = "APPEND * INTO table WHERE etc."
docmd.run strSQL

if it's not clear, give please more details about the tables so I write the whole function for u

Gerard
 
Dim rstUpdate, rstCurrent As Recordset

Set rstCurrent = CurrentDb.OpenRecordset("select * from needs2BForm")
'the above being the form in formation
Set rstAddRec = CurrentDb.OpenRecordset("select * from ReportAddressInfoWithKey")
'the above being the transfer to the new table
'Add New Record Here
With rstAddRec
.AddNew
'Field references to rstSQ to update Questionaire table
![ReportKey] = rstAddRec![Report Key]
![Winton Ref] = rstAddRec![Winton Ref]
![Site Name] = rstAddRec![Site Name]
.Update
End With
 
Sorry the above code was how I was thinkning about doing it but I need to know how the current data in the form can be trapped before I can append to the new table

Tony
 
I dont see ur problem
in the click event from the button do:
tempID = me.ID and restrict the values to this Unique ID
I think better use a Query,
make the query and restrict the IDfield on forms!frmWhatever!IDfield

ButtonClick
set recordset.open queryname, cnn and so on
 
Hi Gerard,

The problem is that a query will not pick up the fields that have been generated on the form.

However thinking about it there is no reason that I can think of why I can't generate then in the query that made this data in the first place.... I will look into it then report back.


Tony
 
Everything works fine, just a case of not seeing the wood for the trees

The code I used was

Dim rstUpdate, rstCurrent As Recordset

Set rstAddRec = CurrentDb.OpenRecordset("select * from ReportAddressInfoWithKey")
'the above being the transfer to the new table
'Add New Record Here
With rstAddRec
.AddNew
rstAddRec![ReportKey] = Me![ReportKey]
rstAddRec![Winton Ref] = Me![Winton Ref]
rstAddRec![Site Name] = Me![Site Name]
.Update
End With

Just got it a little arse about face

Thanks anyway for your input

Tony
 
u mean u have some unbound fields to push into the table?
I know there's a possibility to put them into a query, but never tried. Why not make the values Bound or something?
 
There is no point in bounding them as they don't really do anything until after this point, and from then on they only need to refer to the data I am writing to this table.

So from this point that information is bound along with the other sub records that the user will fill in relating to this form.

Thanks for your help

Tony
 
You could also use an INSERT INTO like this...

Private Sub cmdDoer_Click()
Dim SQL As String, x1 As String, x2 As String, x3 As String, okay As Boolean

okay = IsNull(Me.txtOne)
okay = okay Or IsNull(Me.txtTwo)
okay = okay Or IsNull(Me.txtThree)


If okay Then Exit Sub

'Or Me.txtTwo = Null Or Me.txtThree = Null Then Exit Sub

x1 = Chr(34) & Me.txtOne & Chr(34)
x2 = Chr(34) & Me.txtTwo & Chr(34)
x3 = Chr(34) & Me.txtThree & Chr(34)

SQL = "INSERT into Tabl1 ( one, two, three)"
SQL = SQL & "VALUES ( " & x1 & "," & x2 & "," & x3 & ");"


' MsgBox SQL

DoCmd.RunSQL SQL

Me.txtOne = Null
Me.txtTwo = Null
Me.txtThree = Null

cmdSee_Click

End Sub

rollie@bwsys.net
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top