Auto-create record in a table

[Playbus]

I need to make Access automatically create new records in a table for me.

Just for example:-

Table #1 has 2 fields

PersonID (autonumber)
PersonData (text)

Table #2 has 3 fields

ID (autonumber)
PersonID (number)
MoreData (text)

The database user creates a new record in Table #1 using a form.

I need the database to automatically create a field in Table #2 and fill in PersonID, taking the value from the autonumber field of the same name in Table #1.

The MoreData field can be left blank. I don't need that filled in automatically. I just need a new record to be created automatically in table2 with the PersonID field filled in with the most recently created autonumber.

Help massively appreciated.

 

[GaryPanic]

You are going to run into all sorts of questions like
why

but i would do an.add method on a save button
so either afteupdate on personaldata have a save and .add function to add new record into new table

but I cna see the purests not liking this

 

[Playbus]

I understand that people are going to say "Ooh nooo you don't want to do that!" but two things :-

1. The above is not an exact representation of my database -- I have simplified a LOT. If you can tell me how to do what I asked above, that will be enough for me to work out what to do with my real database (which would require a LOT more space to write about here)

2. I inherited this database, which was terribly made, but due to company politics I have a very short time to get this going. I don't have time to start from scratch, as much as I'd like to.

So, I'd really appreciate if the purists could grit their teeth and let me through with this one hehe.

Thanks for your reply Gary, but I don't understand it. Can you make it any clearer for someone who's a bit thick (me) please ?

I am looking now at a list of Functions in Access 2003 (in the Microsoft Help) and .add isn't in there......?

 

[GaryPanic]

I'll find the code - might take a couple of hours (Its time to go to the Pub)
but check samples for .add in the meantime
g

 

[Playbus]

Thanks Gary, if you can find me example code that does this I'll be very grateful.

Enjoy the pub. I'll see what I can find searching things for .add ....

 

[neileg]

OK, I won't do the purist rant.

When I have wanted to do something like this, I have created a table or tables with the right number of records. I populate the table(s) and append the contents to my main table(s) when complete. I then clear the contents of these tables ready for the next new record.

I can tell you this because I'm just a hacker, not a proper programmer!

 

[GaryPanic]

Dim dbs As DAO.Database
Dim Bmain As DAO.Recordset
Set dbs = CurrentDb
Set Bmain = dbs.OpenRecordset("Bmain")
With Bmain
.AddNew
!QteNo = Me.QteNo
!Qtetype = Me.Qtetype
!QteInsnme = Me.QteInsnme
!Qteinsadd1 = Me.Qteinsadd1
!Qteinsadd2 = Me.Qteinsadd2
!Qteinsadd3 = Me.Qteinsadd3
!Qteinsadd4 = Me.Qteinsadd4
!Qtepostcode = Me.Qtepostcode


found the code
what this is doing is getting the information form the form I am in Me. and the fieldname.qteno and adding this to my history table which is Bmain The !qteno

 

[Playbus]

Whoah

That bit of code just blew my mind .... ok .... I'm not sure how it works (my VB is as basic as basic can be) but I'll have a go and see if I can adapt it to my database .... I'll report back with progress

Thanks Gary

Thanks for your input also neileg .... interesting idea....

 

[Playbus]

Hmm, I've played around a bit, and although I can get the code to not throw up errors when I run it, it doesn't actually add anything to my second table

 

[GaryPanic]

Ok you need to have a button to make it do this on click of button paste the code

also check to see if libary loaded

this is pretty slick

 

[Playbus]

Thanks for your help Gary. I did have a button with the code attached to it (modified for my table names and stuff of course) but when I clicked on it, nothing happened. Not even an error.

But never mind, all is well now. In the end I came up with a workaround which is very simple but had not occurred to my tiny mind at first.

The workaround I think would infuriate the puritans around here even more so I won't embarrass myself .... haha .. it's really a crude solution, but it does what I need to and will keep my boss happy. That's enough for me right now!

Thanks again for the help. I will still keep playing with that code you gave me to see if I can get it to work, but only when I have a bit more time on my hands

 

[GaryPanic]

you have probably used append

this can go a bit tits up
g

 

[Dennisk]

there is a bit missing at the end and that is why it is not updating

.update

 

[GaryPanic]

Aaa. - missed that bit on the copy/paste

d'oh

 

[Playbus]

So that code just literally needs

.update

sticking on the end of it?

 

[GaryPanic]

yeah .... silly old me (as I said I just copy/pasted just the first part)

the acutal code it came from was really longggggggggggggg.
ie every field in my d/base as its my history table

I have main and boundmain with sh*t loads of calucations in it s,plit data out etc working out tax by total, by section , you know how it goes report merge fields that are variable and need to be adjustable blahhh blah so any way this adds your named fields from table A to table B as long as your info is on the me.form
main = bmain

 

[Playbus]

Groovy.

Thanks again for the help everyone, especially Gary

 

[GaryPanic]

this is so slick --once you have wrap your head round it append becomes redundant - you can also put checking routines in their so it will check to see that all the requird fields have been entered if they have not it will bail out and take the user to the bit that requires attention

if you want to go down this ruote let me know and I will copy some proper code for you .

g

 

[GaryPanic]

spot the typos I see at least 3

 

[Playbus]

Yeah, Gary that'd be cool. I'll give you a shout when I get done with my latest project, and you can dazzle me with code. Hehe! You're too nice, y'know!

Cheers