code to create new entry in linked table

  • Thread starter Thread starter aft3rgl0w
  • Start date Start date
A

aft3rgl0w

hey everyone, here's my problem:
I have 2 tables. one contains a list of serial numbers for cd's, along with
other information. the second table is linked to the first, and contains the
serial and the number of spare copies I have. (i cannot incorporate this into
the first table)
I have a form who's source is a query which has all the fields from the
first table plus the extracopies from the second table. I have a + and -
button beneath the extracopies field, to increase or decrease this number.
What I want to do, is if I have no entry in the second table for a serial
number, when I click the + button I want it to create a new entry in the
second table for the current record's serial number and set that value of
extracopies to 1. Any help is much appreciated!!
 
hey everyone, here's my problem:
I have 2 tables. one contains a list of serial numbers for cd's, along with
other information. the second table is linked to the first, and contains the
serial and the number of spare copies I have. (i cannot incorporate this into
the first table)
I have a form who's source is a query which has all the fields from the
first table plus the extracopies from the second table. I have a + and -
button beneath the extracopies field, to increase or decrease this number.
What I want to do, is if I have no entry in the second table for a serial
number, when I click the + button I want it to create a new entry in the
second table for the current record's serial number and set that value of
extracopies to 1. Any help is much appreciated!!
Click to expand...

Use the query to display the records, but not to update them. In the
click event of the command button you can put code that will do the
trick:

dim db as dao.database
dim rs as dao.recordset
set db=currentdb()
set rs= db.openrecordset("tblCopies",dbOpenTable)
rs.index = "idxSerial"
rs.seek "=",txtSerialNo
if rs.nomatch then
rs.addnew
rs!SerialNo = txtSerialNo
rs!Copies = 1
rs.update
else
rs.edit
rs!Copies = rs!Copies + 1
rs.update
endif
me.refresh 'If me refresh doesn't do the trick, try me.recalc
 
i'm getting a "Compile error: User-defined type not defined" error when the
code executes, when i debug it is highlighting the "dim db as dao.database"
line. I should have mentioned, if it makes a difference, that I am using
access 2000 (don't ask why!). Also, if it helps, here is the query I am
using:
[Media] is the main table, [extracopies] is the "link" table
SELECT [Media].[D4_Serial], [Media].[Rec'd Date], [extracopies].[libcopy],
[extracopies].[qty] FROM Media LEFT JOIN extracopies ON
[Media].[D4_Serial]=[extracopies].[d4_serial];
Thanks for your quick reply too!!
 
i'm getting a "Compile error: User-defined type not defined" error when the
code executes, when i debug it is highlighting the "dim db as dao.database"
line. I should have mentioned, if it makes a difference, that I am using
access 2000 (don't ask why!). Also, if it helps, here is the query I am
using:
[Media] is the main table, [extracopies] is the "link" table
SELECT [Media].[D4_Serial], [Media].[Rec'd Date], [extracopies].[libcopy],
[extracopies].[qty] FROM Media LEFT JOIN extracopies ON
[Media].[D4_Serial]=[extracopies].[d4_serial];
Thanks for your quick reply too!!



Use the query to display the records, but not to update them. In the
click event of the command button you can put code that will do the
trick:
Click to expand...
dim db as dao.database
dim rs as dao.recordset
set db=currentdb()
set rs= db.openrecordset("tblCopies",dbOpenTable)
rs.index = "idxSerial"
rs.seek "=",txtSerialNo
if rs.nomatch then
rs.addnew
rs!SerialNo = txtSerialNo
rs!Copies = 1
rs.update
else
rs.edit
rs!Copies = rs!Copies + 1
rs.update
endif
me.refresh 'If me refresh doesn't do the trick, try me.recalc- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Then you have to add DAO to your references. Open a form in code view
and select Tools - References. Select DAO 3.6 for Access 2000.
 
awesome, it works (almost!)!! thanks so much!!
neither me.recalc nor me.refresh works... the new serial will be entered
into the extras table, and the qty will increase, but I have to close and
reopen the form in order for those values to show up on the form.... can I
pick your brain one last time for a possible alternative? thanx again for
the help, it's much appreciated!!
Click to expand...

How about me.requery?
 
I had tried that, and it works, but it flips back to the first record.
However, I added a bit of code so that it can requery then seek to the record
I was at. Thanks again for your help!
 
Back
Top