Code does not 'see' the required related record.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using code I add a customer to the CUST table via code manipulation of
Forms("Customers").
ie using code I open the form, go to a new record, fill in some values AND
then REFRESH the form to write the data to the table.

Further down in the code I want to add some products to the CUSTPRODUCT
table via DAO code ie using CUSTPRODUCT.ADDNEW....CUSTPRODUCT.UPDATE for that
customer.

The and CUST and CUSTPRODUCT tables are related by the customer number (a
classic situation) and CUSTPRODUCT is dependant on a record being in the CUST
table first.

So when CUSTPRODUCT.UPDATE is executed it falls over, stating that a related
record is required in CUST.

The problem is that the record IS there 100%. I pause the code just before
the UPDATE, open the CUST table and it is there alright.

Why cannot the code 'see' the record created by Forms("Customers").Refresh
 
ThomasAJ said:
Using code I add a customer to the CUST table via code manipulation of
Forms("Customers").
ie using code I open the form, go to a new record, fill in some values AND
then REFRESH the form to write the data to the table.
Click to expand...

Instead of Refresh, use:

Me.Dirty = False
 
In
ThomasAJ said:
Using code I add a customer to the CUST table via code manipulation of
Forms("Customers").
ie using code I open the form, go to a new record, fill in some
values AND then REFRESH the form to write the data to the table.

Further down in the code I want to add some products to the
CUSTPRODUCT table via DAO code ie using
CUSTPRODUCT.ADDNEW....CUSTPRODUCT.UPDATE for that customer.

The and CUST and CUSTPRODUCT tables are related by the customer
number (a classic situation) and CUSTPRODUCT is dependant on a record
being in the CUST table first.

So when CUSTPRODUCT.UPDATE is executed it falls over, stating that a
related record is required in CUST.

The problem is that the record IS there 100%. I pause the code just
before the UPDATE, open the CUST table and it is there alright.

Why cannot the code 'see' the record created by
Forms("Customers").Refresh
Click to expand...

Show your code, please.

By the way, why add a record to a table by manipulating a form? Why not
add it directly via DAO?
 
Code too lengthy to show.

You asked "why add a record to a table by manipulating a form?"

The user 'drops' an Outlook email into a form. ("Form_Drop").
Form_Drop opens the target form ("Form_Target"),
pre-fills some info into it from the email
The user adds some info themselves into Form_Target
Clicks a button on Form_Target to say they are done with this email
Form_Drop resumes executing some code (this is where the error occurs)
The Form_Drop grabs another email etc.
 
In
ThomasAJ said:
Code too lengthy to show.

You asked "why add a record to a table by manipulating a form?"

The user 'drops' an Outlook email into a form. ("Form_Drop").
Form_Drop opens the target form ("Form_Target"),
pre-fills some info into it from the email
The user adds some info themselves into Form_Target
Clicks a button on Form_Target to say they are done with this email
Form_Drop resumes executing some code (this is where the error occurs)
The Form_Drop grabs another email etc.
Click to expand...

Okay, that makes sense. But without seeing the code that tries (and
fails) to add a related record to CUSTPRODUCT, it's hard to say what's
wrong. I've seen this sort of problem when the code opens a separate
database connection from the one that Access is using. You aren't using
the DAO OpenDatabase method, are you?
 
Thanks Dirk
You said "You aren't using the DAO OpenDatabase method, are you?"

NO. It's just the simple rsCUSTPRODUCT.OpenRecordSet("CUSTPRODUCT") in
Form_Drop.

There is a complication that might be affecting it:
Table CUSTPRODUCT is the RecordSource for a Subform (in a TAB) WITHIN
Form_Target. This is NOT shown during this 'Email Drop' action.

This is the only thing I can think of that is 'unusual'.
 
In
ThomasAJ said:
Thanks Dirk
You said "You aren't using the DAO OpenDatabase method, are you?"

NO. It's just the simple rsCUSTPRODUCT.OpenRecordSet("CUSTPRODUCT") in
Form_Drop.
Click to expand...

What would rsCUSTPRODUCT be, then? I'd expect a recordset object from
the prefix, but this doesn't seem like the sort of situation where you'd
be opening a recordset from another recordset, and the syntax isn't
right for that, anyway.

Helping you with this would be a lot easier if you would post the
relevant code.
There is a complication that might be affecting it:
Table CUSTPRODUCT is the RecordSource for a Subform (in a TAB) WITHIN
Form_Target. This is NOT shown during this 'Email Drop' action.

This is the only thing I can think of that is 'unusual'.
Click to expand...

That doesn't sound like it would have any effect on the matter.
 
Sorry the open recordset should have been:
Set rsCUSTPRODUCT = myDB.OpenRecordSet("CUSTPRODUCT").

You said "opening a recordset from another recordset" but I'm not sure why
think that. Anyway Set rsCUSTPRODUCT is being performed within Form_Drop
which is the 'controlling' code.

In summary 'data movement wise'

Table CUSTPRODUCT is dependent on table CUST.
Form_Target updates table CUST via its RecordSource property.
(Table CUSTPRODUCT is the Record Source for a subform in Form_Target)
Form_Drop wants to update table CUSTPRODUCT via DAO code. (fails)

I'll post the code soon.
 
In
ThomasAJ said:
Sorry the open recordset should have been:
Set rsCUSTPRODUCT = myDB.OpenRecordSet("CUSTPRODUCT").
Click to expand...

That makes more sense, and is what I would have expected. And what line
of code is setting myDB?
 
Back
Top