Records in a query between a one-many rel. not allowing data entry

[Giem]

Hey all,

I'm fairly new to database design, and have a problem with a query to add
new information into tables in Access 2003.

I have a repair table in which I am trying to add repair information, and at
the same time information to an RMA table that has RMA information. The
tables both have an "RMAID" field, which is an autonumber and is the primary
key in the RMA table. The repair table is the "many" side and the RMA table
is the "one" side. The RMA might have more than one repair tied to it. So far
nothing unusual from what I have seen in example databases.

I am trying, with a query, just to add a new record to a field in the REPAIR
table, and a field in the RMA table. Of course it won't let you unless you
add a new record in RMA inside the query first, then save the record, then
going back and add the REPAIR data in the record. I want to be able to add a
new record across tables at the same time without having to do this. That is,
I am trying to create a new RMA number every time I create a new repair.

Is there a way around this or is there some other methodology I should use
altogether?

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You should be using subforms.

Create a main form for the RMA table data.
Create a subform for the repair table data.
Put the subform on the main form - link by the RMAID.
When you move to a new record you will automatically create a new RMAID.
When you jump into the new record's subform (repairs) the new RMAID will
be saved and the data for the new repair record will have that new RMAID
automaticially assigned to it.

Read up on subforms in the Access Help files.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR5lse4echKqOuFEgEQL9fgCg8S7ft/TZ1ziqvuNHOH0V1AQMT1kAoIRw
5p/SYU0f408LVB3u0tvOXbQh
=Sb0O
-----END PGP SIGNATURE-----

 

[Jamie Collins]

You should be using subforms.

No doubt that's good advice but, well, this is the 'Queries' group and
the OP did explicitly ask to do this in a Query...

The trick is to create a Query (a.k.a. VIEW) joining the two tables on
the autonumber PK column including the non-nullable columns without
defaults, then INSERT INTO the VIEW e.g.

CREATE TABLE RMA (
RMAID INTEGER DEFAULT GenUniqueID() NOT NULL PRIMARY KEY,
rma_data_col INTEGER NOT NULL
)
;
CREATE TABLE REPAIR (
RMAID INTEGER NOT NULL REFERENCES RMA (RMAID),
repair_data_col INTEGER NOT NULL
)
;
CREATE VIEW RmaRepair
AS
SELECT T1.RMAID AS RMAID_1, T1.rma_data_col,
T2.RMAID AS RMAID_2, T2.repair_data_col
FROM RMA AS T1
INNER JOIN REPAIR AS T2
ON T1.RMAID = T2.RMAID
;
INSERT INTO RmaRepair (rma_data_col, repair_data_col)
VALUES (1, 1)
;
INSERT INTO RmaRepair (rma_data_col, repair_data_col)
VALUES (2, 2)
;
INSERT INTO RmaRepair (rma_data_col, repair_data_col)
VALUES (3, 3)
;

To demo the above, the following VBA creates a new mdb in your temp
folder:

Sub AutoInsert()
' Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
..Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim Sql As String
Sql = _
"CREATE TABLE RMA (" & vbCr & "RMAID INTEGER" & _
" DEFAULT GenUniqueID() NOT NULL" & _
" PRIMARY KEY, " & vbCr & "rma_data_col INTEGER" & _
" NOT NULL" & vbCr & ")"
..Execute Sql
Sql = _
"CREATE TABLE REPAIR (" & vbCr & "RMAID INTEGER" & _
" NOT NULL REFERENCES RMA (RMAID)," & _
" " & vbCr & "repair_data_col INTEGER NOT" & _
" NULL" & vbCr & ")"
..Execute Sql
Sql = _
"CREATE VIEW RmaRepair " & vbCr & "AS " & vbCr & "SELECT" & _
" T1.RMAID AS RMAID_1, T1.rma_data_col," & _
" " & vbCr & "T2.RMAID AS RMAID_2, T2.repair_data_col" & _
" " & vbCr & "FROM RMA AS T1 " & vbCr & "INNER JOIN REPAIR" & _
" AS T2 " & vbCr & "ON T1.RMAID = T2.RMAID"
..Execute Sql
Sql = _
"INSERT INTO RmaRepair (rma_data_col," & _
" repair_data_col) " & vbCr & "VALUES (1," & _
" 1)"
..Execute Sql
Sql = _
"INSERT INTO RmaRepair (rma_data_col," & _
" repair_data_col) " & vbCr & "VALUES (2," & _
" 2)"
..Execute Sql
Sql = _
"INSERT INTO RmaRepair (rma_data_col," & _
" repair_data_col) " & vbCr & "VALUES (3," & _
" 3)"
..Execute Sql
Sql = _
"SELECT * FROM RmaRepair"
Dim rs
Set rs = .Execute(Sql)
MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[Giem]

Perfect! That did the trick. Thanks Jamie.



Thank you as well MGFoster, this is certainly helpful.

Giem