Re: Allen Browne - Help with duplicating Form & Subform!
- From: Eka1618 <Eka1618@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 1 Oct 2008 05:22:06 -0700
Hi Allen,
This is what printed in the Immediate Window:
INSERT INTO tblPattern( PAT_NO, PAT_SIZE, QUANTITY ) SELECT PAT_NO,
PAT_SIZE, QUANTITY FROM tblPattern WHERE REQUEST_NO = 133;
I ran it seperatly in a query and It said cannot append 1 record into table
due to key violations. I am certain that it is because of the request number,
it is not inserting anything into it.
When I created table pattern I wanted to create a unique index on the entire
row for a table, but the only way I know how to do this in Access is to
create the composite PK which includes the PAT_NO, PAT_SIZE and REQUEST_NO.
This way I can tell the DB that there needs to be a unique pattern record for
each request, not for each row in tblPattern.
Please let me know if you have any other thoughts... Thanks again for all
your help!
--
~Erica~
"Allen Browne" wrote:
Immediately before the Execute line, temporarily add this line:.
Debug.Print strSql
When it fails, open the Immediate Window.
Copy what you see there, and paste it into a new query.
Try running the query.
See if you can pin down what's wrong.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Eka1618" <Eka1618@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C0C9B4A4-FC30-404D-A476-B25F79651FD4@xxxxxxxxxxxxxxxx
Hi Allen,
You Said : Appending to the RecordsetClone of the form does not move it to
a
new record.
So getting the value by using the current event will not work then (if
that
is what you mean). This is what I originally thought and so I do not
understand what you mean by getting the values from somewhere else. Do you
mean by creating a query and using that recordset, or using the table?
I have done another test and I think that perhaps I am wrong about what is
going on durring the code. I used the following:
If Me.frmPattern.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tblPattern( PAT_NO, PAT_SIZE, QUANTITY ) " & _
"SELECT PAT_NO, PAT_SIZE, QUANTITY FROM tblPattern WHERE REQUEST_NO = " &
Me.REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If
It told me that a PK cannot be null. As a test I ran this as a seperate
query and used the request number of 133 and I do have 1 record that
matches.
I am wondering if it is not the PAT_NO and PAT_SIZE at all and if it is
the
REQUEST_NO. It would make more sense to me to say insert pattern number,
size
and quantity where the request number = the request that I am duplicating.
However, I think I need the code to give me that new Request Number value
so
that I can append that as well.
I think the code should read more like this:
If Me.frmPattern.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tblPattern( PAT_NO, PAT_SIZE, QUANTITY, REQUEST_NO )
"
& _
"SELECT PAT_NO, PAT_SIZE, QUANTITY, (NEW REQUEST NUMBER) FROM tblPattern
WHERE REQUEST_NO = " & Me.REQUEST_NO & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
End If
This sort of makes more sense, but I do not think it could work at the
same
time. tblPattern is looking for REQUEST_NO, PAT_NO and PAT_SIZE since they
are a composite PK and I do not think it is getting that new REQUEST_NO
value.
I think this better explains whats going on, please let me know what you
think when you get a chance, Thank You!
--
~Erica~
"Allen Browne" wrote:
Not sure I followed.
Appending to the RecordsetClone of the form does not move it to a new
record.
The subform could be at a new record if there are no related records
there,
or if it happens to be at the new record.
"Eka1618" <Eka1618@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9038C847-363C-49CE-B229-BE3875B161B9@xxxxxxxxxxxxxxxx
Hi Allen,
Since you last response I have not been able to work on this issue
again
until today.
One important thing I forgot to mention the other day is that the
records
that are being duplicated are exsisting records from an earlier time.
When the parent for opens, I prompt the user to find the request they
want
to duplicate. If there are patterns associated with the request, then
the
records that appear in frmPattern were already saved, so it does not
seem
possible for frmPattern to be at a new record (with the example I have
been
using). I have frmPattern Data Entry Property set to No.
However, it is possible for it to be at a new record, but in the
example
that I have been trying to test this with, the pattern information has
already been saved. This is why I am confused as to why it says it is
NULL.
I am going to try using form_current event on frmPattern to set PAT_NO
and
PAT_SIZE to variables accessible in the parent form. I just wanted to
mention
the fact the the records that I am trying in my example are exsisting
records
at the time the duplicate button is clicked.
- Prev by Date: If "None" then no others, If others then no "None"
- Next by Date: Re: Allen Browne - Help with duplicating Form & Subform!
- Previous by thread: If "None" then no others, If others then no "None"
- Next by thread: Re: Allen Browne - Help with duplicating Form & Subform!
- Index(es):
Relevant Pages
|
