...Relation with tables problem (continue)

[kon]

I have the suspition that only in Access XP the following message appears. I
created the file in Access 2000 and when I was using access 2000 was fine.
After I install Access xp then the message appear "You cannot add
or change a record because a related record is required in table". Even If I
save the file with Access 2000 format and run it in other computer having
Access 2000 the problem appears. My old access 2000 file works fine in
Access 2000 and with problems in Windows XP. I believe that nothing has to
do with references. Please reply me If I am wrong or not.

 

[Duane Hookom]

I suspect that you are creating a new record in a "master table" form and
have some process that adds a new record to a "child table". You may need to
force the main record to save prior to running the process that adds the
child record.

 

[kon]

Sorry but I would need a simple example of what you mean. Besides Can you
explain me why in Access 2000 when I save the file using Access 2000 I don't
encounter that message ?

 

[Duane Hookom]

You show me your code and I'll show you mine. How do you insert records into
both tables?

 

[kon]

I have a form based in headmaster table and a subform based in master.
Headmaster and master are linked together in primary keys, datefld and
customerid, where workdescription is only primary key for master also. So
Headmaster has 2 primary keys and master 3. I click a button named enter and
I use the me.dirty = false to save the record in Headmaster and aftewards I
run that code
sqlstring = "INSERT INTO master ( datefld,customerid,workdescription )SELECT
#" & vardate & "#," & varcustomerid & ",workdescription FROM works"
cncurrent.Execute (sqlstring)
So why the first time before any changes that I made in my database worked
fine and now I have that message?

 

[Duane Hookom]

I don't see the code for Me.Dirty = False or where varDate, varcustomerid
come from. Also what is "workdescription" in the SQL statement? What is the
table or query "works"?

Have you attempted to debug your code by setting a breakpoint or using
debug.print to check the value of sqlstring?

 

[kon]

Yes I use the debug.print and I checked the code. the string workdescription
is a field from the tables works.

 

[kon]

I have the table headmaster with 2 primary keys
customerid and date
Another table master with 3 primary keys
customerid, date and work description
The two tables have a relationship one to many connecting the 2 primary keys
of headmaster with the 2 only primary keys of master.
I want to use VBA code and SQL strint insert to in the master keys to fill
the workdescription (3rd primary key) with records from another table.
How can I do this.
I used me.dirty = false in the headmaster and aftewards I tried the SQL
string. Then the message appears. How can I enter data in a relationshiped
tables?

 

[kon]

I found the problem but how can I fix it.
In the headmaster the date entered as dd/mm/yyyy witch is ok for me. Greek
date format. But in the variable
when I tried to create the sql string is mm/dd/yyyy. How can I change it to
dd/mm/yyyy ; shall I use the format function? or do i have to check an
option in Access

 

[Duane Hookom]

I see you started another thread with this question so I won't continue this
one...

 

[Tom Wickerath]

Hi Kon,

I think you will find this article by MVP Allen Browne most helpful:

International Dates in Access
http://allenbrowne.com/ser-36.html

Also, in a previous message in this thread, you made the following statement:

"I have the table headmaster with 2 primary keys customerid and date Another
table master with 3 primary keys customerid, date and work description"

A table can have only one primary key. A primary key can be made up of one
or more fields. When more than one field is involved, we call it a combined
field primary key.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

I found the problem but how can I fix it.
In the headmaster the date entered as dd/mm/yyyy witch is ok for me. Greek
date format. But in the variable
when I tried to create the sql string is mm/dd/yyyy. How can I change it to
dd/mm/yyyy ; shall I use the format function? or do i have to check an
option in Access