painted corner - updating a subdatasheet

[Guest]

I need help creating a record in a related table (Subdatasheet) but I'm not
too sure of the best method. Here's the need:

The value as entered into [tblLocations].[txtLocationID] via the control
[Forms]![frmFacilitiesLocs]![cbFacilityID] needs to also be entered in
[tblLocationsLocationIDs].[txtLocID] - but how? Maybe the first question is
why?

The reason for this is complex but I'll try to map it out. [tblLocations]
houses info for 3 types of locations: Facility, Customer, and Supplier. The
"Facility" type are already assigned unique ID numbers and will have only one
address while the other 2 types can have whatever ID number I want to give.
These ID numbers relate to another ID table as the Customer and Supplier
types can have many ID numbers. This second ID table relates to an address
table as each ID can have many addresses. WHEW! Ultimately, what I've done
pool the addresses for all 3 location types into one table. I'm not sure if
this is the best approach/design but it's what I needed and it's working.

Now to the problem. I suppose I need to start with the form in question.

[frmFacilitiesLocs]:
SELECT tblLocations.txtLocationID, tblLocations.Name, tblLocations.Class,
tblLocations.Group, tblLocations.Description, tblLocations.Notes,
tblLocationsLocationIDs.txtLocID,
tblLocationIDsAddresses.numLocationAddressID, tblLocationIDsAddresses.Type,
tblLocationIDsAddresses.Address, tblLocationIDsAddresses.City,
tblLocationIDsAddresses.StateOrProvince, tblLocationIDsAddresses.PostalCode,
tblLocationIDsAddresses.CountryRegion, tblLocationIDsAddresses.Notes
FROM tblLocations INNER JOIN (tblLocationsLocationIDs INNER JOIN
tblLocationIDsAddresses ON tblLocationsLocationIDs.numLocID =
tblLocationIDsAddresses.numLocID) ON tblLocations.txtLocationID =
tblLocationsLocationIDs.txtLocationID
WHERE (((tblLocations.Class)="FAC"))
ORDER BY tblLocations.txtLocationID;

So to state the problem again I need to somehow get the value as entered
into [tblLocations].[txtLocationID] via the control
[Forms]![frmFacilitiesLocs]![cbFacilityID] into
[tblLocationsLocationIDs].[txtLocID].

I thought maybe an invisible control on the form...? Any ideas/help getting
me out of this painted corner would be greatly appreciated!!!

 

[Rick A.B.]

So to state the problem again I need to somehow get the value as entered
into [tblLocations].[txtLocationID] via the control
[Forms]![frmFacilitiesLocs]![cbFacilityID] into
[tblLocationsLocationIDs].[txtLocID].

I thought maybe an invisible control on the form...? Any ideas/help getting
me out of this painted corner would be greatly appreciated!!!

--www.Marzetti.com

John,

If the data will never change and you want to create a record in the
tblLocationsLocationsID table when you create one in tblLocations,
open a record set on the table you wish to add the field to as
follows;
'Using DAO
Dim db As DAO.Database

Set db = CurrentDb
Set rst = db.OpenRecordset("tblLocationsLocationsID",
dbOpenDynaset, dbSeeChanges)
rst.AddNew
rst!txtLocID = Forms!frmFacilitiesLocs!cbFacilityID
rst.Update
Set rst = Nothing
Set db = Nothing


'Using ADO
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.Open "tblLocationsLocationsIDs", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

rst.AddNew
rst!txtLocID = Forms!frmFacilitiesLocs!cbFacilityID
rst!TermID = 2

rst.Update
rst.Close

Set rst = Nothing


The logical place to put the code would be in the afterupdate event of
cbFacilityID. If the data will change, then you will need a different
routine somewhere else to update the data instead of adding a new
record. You would also need to use a condition to tell the code to
only fire if there is no record already in tblLocationsLocationsID.
Hope that helps. Remember to watch out for word wrap.
Rick

 

[Guest]

WOWZERS! Thanks, Rick!!!
--
www.Marzetti.com

So to state the problem again I need to somehow get the value as entered
into [tblLocations].[txtLocationID] via the control
[Forms]![frmFacilitiesLocs]![cbFacilityID] into
[tblLocationsLocationIDs].[txtLocID].

I thought maybe an invisible control on the form...? Any ideas/help getting
me out of this painted corner would be greatly appreciated!!!

--www.Marzetti.com

John,

If the data will never change and you want to create a record in the
tblLocationsLocationsID table when you create one in tblLocations,
open a record set on the table you wish to add the field to as
follows;
'Using DAO
Dim db As DAO.Database

Set db = CurrentDb
Set rst = db.OpenRecordset("tblLocationsLocationsID",
dbOpenDynaset, dbSeeChanges)
rst.AddNew
rst!txtLocID = Forms!frmFacilitiesLocs!cbFacilityID
rst.Update
Set rst = Nothing
Set db = Nothing


'Using ADO
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.Open "tblLocationsLocationsIDs", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic

rst.AddNew
rst!txtLocID = Forms!frmFacilitiesLocs!cbFacilityID
rst!TermID = 2

rst.Update
rst.Close

Set rst = Nothing


The logical place to put the code would be in the afterupdate event of
cbFacilityID. If the data will change, then you will need a different
routine somewhere else to update the data instead of adding a new
record. You would also need to use a condition to tell the code to
only fire if there is no record already in tblLocationsLocationsID.
Hope that helps. Remember to watch out for word wrap.
Rick