update table based on existing record

  • Thread starter Thread starter Joan
  • Start date Start date
J

Joan

Hi there,

Is there a property or something I can use to update a
table based on a current record. i.e. I have a
receiptline table that I would like the user to be able
to create a new line for a new serial number on the part
by pressing a cmd button. I thought I had it working
using an INSERT SQL statement with a where clause of
tblrecieptline.recieptlineid = me.recieptlineid. I
cannot use recieptlineid on the form though because as
soon as I add that field to the qry that the subform is
based on it undoes my grouping.

To futher explain (background). I have the user able to
hit a cmd button on PO to create the receiptheader and
receiptline records based on the PONumber they are on.
This works except if there is more then one receipt
against the PO it creates duplicate records (because of
the previous PONumber). My ReceiptLines subform is built
based on a qry which groups the receiptlines together
based on the receiptno from the receipthdr (had to do
this way because of above). So if I add the
receiptlineid field to my qry for the subform, it then
returns the duplicate records I am trying to avoid based
on multiple receipts on one PO.
I don't know if I explained myself well enough, if more
info is needed let me know. If there is a way to perform
an insert based on current record property or
something.....
How do I do it?
Thanks in advance for your help. All is appreciated!
 
To shorten the below - is there a way I can use
the .currentrecord property? I have a docmd.runsql event
running behind a cmd button on a form. I'm not sure how
to incorporate the .currentrecord property into the sub
behind the cmd button - can I?

Thanks.
 
I'm not sure you have give enough detail, but it sounds to me that you
have a database
design problem. You master record is using PO number for the primary key.
The detail records should have for keys, PO number and a sequence number or
autonumber. These two keys combined should be the primary key for the
detail record.

On your subform you should tell Access what the parent/child fields
are. This will be the
PO number from the master record (parent), to the PO number in the
detail record (child).
At this point Access will add as many detail records for a given master
record when the user
clicks an Add button. or when they navigate to the new record position
in the table.

You should not need to do an additional Insert SQL statement.

HTH
Ron
 
Hi Roger,
Thanks for your reply. I think I have a design issue as
well but I don't know a way around it.
I have POHeader and POLines tables linked by PONumber.
Then I have a ReceiptsHdr and ReceiptLines tables.
On the PO Form I have a cmd button which creates the
entry in the receipthdr and receiptline table based on
the POheader the user is on (this is done via SQL
statement behind cmd button on POForm)

ie. 1 PO with 1 PO Line, would create 1 record in
RecieptHdr and 1 record in RecieptLine. ReceiptNo. is
autonumber primary key in Receipt Header.

My first problem started when I tried to create a second
receipt from the same PO. it would then create 1
ReceiptHdr (as above) but then 2 ReceiptLines (I think
because the insert statement to create the record
is 'where POnumber = formPONumber', and since there is a
previous Receipt with that PONumber is is creating the
line twice. I don't know a way around this so I created
a Qry to group the records and use that as for my receipt
line form. It seems to work but I can't update the rec.
no into the receiptline table to create the link.

Anyway, I got the above to work. My next step was to add
a cmd button to the receiptline form, so the user could
easily copy the line they are on creating a new record,
with the same item info, but different serial number. I
thought I had this working - insert based on receiptnoid
= form.receiptnoid = but when I added the receiptnoid
field to my qry that the receiptline form is based on I
get the duplicate records again.

I don't know how to solve the db issue! It's driving me
nuts. If you can help....

Thanks
 
Back
Top