Code does not 'see' the required related record.

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
 
S

Stuart McCall

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.

Instead of Refresh, use:

Me.Dirty = False
 
D

Dirk Goldgar

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

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?
 
G

Guest

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.
 
D

Dirk Goldgar

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.

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?
 
G

Guest

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'.
 
D

Dirk Goldgar

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.

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'.

That doesn't sound like it would have any effect on the matter.
 
G

Guest

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.
 
D

Dirk Goldgar

In
ThomasAJ said:
Sorry the open recordset should have been:
Set rsCUSTPRODUCT = myDB.OpenRecordSet("CUSTPRODUCT").

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top