Create records in a table

[Guest]

Hello

Hopefully someone can help me because this is beyond my ability

Through the use of a form the user will input data into two temp tables ("Stress Report Temp Tbl 01" & "Stress Report Temp Tbl 02"). "Stress Report Temp Tbl 01" contains one field ("Dwg Id") and "Stress Report Temp Tbl 02" also contains one field ("Hyperlinks"). I need to create a code that will add new record into a third table ("Stress Report Tbl") based on the information in the two temp tables

Let me explain. The "Stress Report Tbl" contains three fields ("Autonumber", Drawing Id" & "Stress Report"). I need a macro that will create the hyperlinks specified in the "Stress Report Temp Tbl 02" "Hyperlinks" column for each of the "Dwg Id in the "Stress Report Temp Tbl 01"

As such, if they're were 3 dwg id and 5 hyperlinks a total of 15 entries would be created

Thank you

Daniel

 

[Guest]

Hello again

This is what I came up with ... it seems to work but if anyone has a better way please let me know

Private Sub Command3_Click(
'for each drawing id createa set of hyperlink
'once finished deleteall records from temp table
DoCmd.Close 'closes the form so the user doesn't see what is going o

Dim db As Databas
Dim rst1, rst2, rst3 As DAO.Recordse

'Cycle through the Dwg Id
Set db = CurrentDb(
Set rst1 = db.OpenRecordset("Stress Report Temp Tbl 01"
' Check first to see if there are any rows
With rst
If .RecordCount > 0 The
' Move to the end
.MoveLas
' Loop back towards the beginning
Do Until .BO
'Debug.Print ![Dwg Id
'**********************************************************************************************************
'Cycle through the hyperlink
Set rst2 = db.OpenRecordset("Stress Report Temp Tbl 02"
' Check first to see if there are any rows
With rst
If .RecordCount > 0 The
' Move to the end
.MoveLas
' Loop back towards the beginning
Do Until .BO
'Debug.Print ![Hyperlinks
'**********************************************************************************************************
'Create the entries based on the temp tabl
Set rst3 = db.OpenRecordset("Stress Report Tbl"
With rst
.AddNe
![Drawing Id] = rst1![dwg id
![Stress Report] = rst2![Hyperlinks
.Updat
End Wit
'**********************************************************************************************************
.MovePreviou
Loo
End I
.Clos
End Wit
'**********************************************************************************************************
.MovePreviou
Loo
End I
.Clos
End Wit

'Clear temp tables for the next us
CurrentDb.Execute "delete * FROM [Stress Report Temp Tbl 01]
CurrentDb.Execute "delete * FROM [Stress Report Temp Tbl 02]

End Su

Thank yo

Daniel

 

[Rodrigo]

Just make an insert query with both tables as the source. But don't join the
tables by a field.
This query will generate a cartesian product of the tables.

This is the worst kind of query you can make, but it will work for what you
need.

table1
id
name

table 2
hyperlink

table 3
id
name
hyperlink

query,
insert into table3 select id, name, hyperlink from table1, table2

Rodrigo.

Hello,

Hopefully someone can help me because this is beyond my ability.

Through the use of a form the user will input data into two temp tables

("Stress Report Temp Tbl 01" & "Stress Report Temp Tbl 02"). "Stress Report
Temp Tbl 01" contains one field ("Dwg Id") and "Stress Report Temp Tbl 02"
also contains one field ("Hyperlinks"). I need to create a code that will
add new record into a third table ("Stress Report Tbl") based on the
information in the two temp tables.

Let me explain. The "Stress Report Tbl" contains three fields

("Autonumber", Drawing Id" & "Stress Report"). I need a macro that will
create the hyperlinks specified in the "Stress Report Temp Tbl 02"
"Hyperlinks" column for each of the "Dwg Id in the "Stress Report Temp Tbl
01".

 

[John Vinson]

Hello,

Hopefully someone can help me because this is beyond my ability.

Through the use of a form the user will input data into two temp tables ("Stress Report Temp Tbl 01" & "Stress Report Temp Tbl 02"). "Stress Report Temp Tbl 01" contains one field ("Dwg Id") and "Stress Report Temp Tbl 02" also contains one field ("Hyperlinks"). I need to create a code that will add new record into a third table ("Stress Report Tbl") based on the information in the two temp tables.

Let me explain. The "Stress Report Tbl" contains three fields ("Autonumber", Drawing Id" & "Stress Report"). I need a macro that will create the hyperlinks specified in the "Stress Report Temp Tbl 02" "Hyperlinks" column for each of the "Dwg Id in the "Stress Report Temp Tbl 01".

As such, if they're were 3 dwg id and 5 hyperlinks a total of 15 entries would be created.

And that's what you want? If there were 10 dwg id's and 20 hyperlinks
you'ld want all 200 possible combinations?

If so, you don't need any macro or looping at all: just a (normally
undesirable) Cartesian query. Create a query by adding both temp
tables to the query grid (btw I *really* worry about your tablenames
:-{( ), with NO join line. Change it to an Append query and select
Stress Report Tbl as the output table. Append [dwg id] to [Drawing ID]
and [Hyperlinks] to [Stress Report]; run the query.

 

[Guest]

John,

Could you explain why you worry about my table names.... Thanks it is appreciated! What should I be trying to acheive.

Daniel

 

[John Vinson]

John,

Could you explain why you worry about my table names.... Thanks it is appreciated! What should I be trying to acheive.

Daniel

Long table names with special characters (blanks) are inefficient and
much more difficult to use; in addition, upgrading to SQL/Server or
Oracle will be problematic since they do not permit blanks in object
names. You can set the Caption property of a table (or form, or
whatever other object) so that the user can see something verbose and
meaningful while the database sees something compact and unambiguous
(such as tmpTblIDs).