I have two tables (tblORC and tblTestShot). tblORC has the following fields: [ID],[Date/Time],[Item Number],[Discrete Job] and a lot more that aren't important. tblTestShot has the following fields: [ID],[Test Shot ID],[Test Shot Number] and some more fields about that "test shot".
I have a form based on tblORC with a subform based on tblTestShot. When entering in the information from the form into tblORC, I want to automatically make the [Test Shot ID] field of tblTestShot equal to the [ID] field of tblORC for all entries made into the subform.
I set up the queries so that it shows the correct "test shots" for each record.
The problem I am having is the best method of writing the correct [Test Shot ID] value into tblTestShot when based on the current [ID] value (automatically generated in the table tblORC). Here's the approach I thought of, but I am getting an error.
In the AfterUpdate event of the field entered first on my main form:
It works the first time I open the form, but I finish one record and try to add another I get the following error: Error 3422: Another user has the table open, on the line td.Fields![Test Shot ID].DefaultValue = Me.txtID.
I tried closing the table before running the above code, but that does not work. I feel like I'm using the wrong approach to this problem b/c it doesn't seem very failsafe. Any suggestions would be greatly appreciated.
I have a form based on tblORC with a subform based on tblTestShot. When entering in the information from the form into tblORC, I want to automatically make the [Test Shot ID] field of tblTestShot equal to the [ID] field of tblORC for all entries made into the subform.
I set up the queries so that it shows the correct "test shots" for each record.
The problem I am having is the best method of writing the correct [Test Shot ID] value into tblTestShot when based on the current [ID] value (automatically generated in the table tblORC). Here's the approach I thought of, but I am getting an error.
In the AfterUpdate event of the field entered first on my main form:
Code:
Dim db As Database, td As TableDef
Set db = CurrentDb
Set td = db.TableDefs![tblTestShot]
td.Fields![Test Shot ID].DefaultValue = Me.txtID
Set td = Nothing
Set db = NothingIt works the first time I open the form, but I finish one record and try to add another I get the following error: Error 3422: Another user has the table open, on the line td.Fields![Test Shot ID].DefaultValue = Me.txtID.
I tried closing the table before running the above code, but that does not work. I feel like I'm using the wrong approach to this problem b/c it doesn't seem very failsafe. Any suggestions would be greatly appreciated.
