Enter info once for 3 tables

[Harry_Imp]

Hi all,

I have 3 tables in my database,

They all contain the same info, but i need all 3.

I was just wondering, to save me entering the same information into each table individually, is there anything i can do when i enter it once, and it goes into all tables?

Harry

 

[jdraw]

Please tell us more about your database and tables.

 

[thechazm]

Yes you can do this but why in the world you need three seperate tables with the same information sounds a little odd. Anyway,

If you want there are a couple ways to do this but all the ones I know of consist of vba code.

You can do something like this for each controls [After Update] Event:
dim rsc as new adodb.recordset

rsc.open "This would be a sql select statement that would check to see if the record is already there or not", currentproject.connection

if rsc.eof = false then
docmd.runsql "This would be an update query that would find the record and update the information"
else
docmd.runsql "This would be an append query that would add the new record and information from the control"

end if

rsc.close
set rsc = Nothing

I did not include any error checking or notes with this since I just typed it out in the quick reply but it should get you started.

Hope this helps,

TheChazm

 

[Harry_Imp]

Yes you can do this but why in the world you need three seperate tables with the same information sounds a little odd. Anyway,

If you want there are a couple ways to do this but all the ones I know of consist of vba code.

You can do something like this for each controls [After Update] Event:
dim rsc as new adodb.recordset

rsc.open "This would be a sql select statement that would check to see if the record is already there or not", currentproject.connection

if rsc.eof = false then
docmd.runsql "This would be an update query that would find the record and update the information"
else
docmd.runsql "This would be an append query that would add the new record and information from the control"

end if

rsc.close
set rsc = Nothing

I did not include any error checking or notes with this since I just typed it out in the quick reply but it should get you started.

Hope this helps,

TheChazm

Thanks mate, but i am totally new to Access, and I am wondering what on earth all this means?


Sorry
Harry

 

[thechazm]

Here is a little bit of an explination but its a bit of a learning curve.

//' This sets up a variable to be used in the code as a independent recordset(Meaning it keeps the data in it just like a table would)

dim rsc as new adodb.recordset

//' This part of the code would add the records into the recordset with whatever criteria you specify using SQL code. Eg. "Select * from tblpersonnel WHERE (((tblPersonnel.[Location]) = """ & "Ohio" & """));"

rsc.open "This would be a sql select statement that would check to see if the record is already there or not", currentproject.connection

//' This part would do a small check to make sure there are records with the criteria you specified above in your SQL statement
if rsc.eof = false then

//' This one runs a sql statement that would update a record with the information. Eg. "UPDATE tblPersonnel SET tblPersonnel.[Location] = """ & me.location & """ WHERE tblPersonnel.[Personnel Number] = " & me.[Personnel Number].value & ";"

docmd.runsql "This would be an update query that would find the record and update the information"

//' This starts the Adding new record if one does not exist
else
docmd.runsql "This would be an append query that would add the new record and information from the control"

//' This ends the if statement
end if

//' Some cleanup code
rsc.close
set rsc = Nothing

 

[CraigDolphin]

Is this the same database as your other thread Harry?
http://www.access-programmers.co.uk/forums/showthread.php?t=207852

 

[Harry_Imp]

Yes it is.

thechazm, but what do i do wlth all that info? as i say i have only recently started using access, nothing that complicated? #



Harry

 

[thechazm]

Well to get you started I would just select a control and view its properties.

In the properties window at the top you should see some tabs like (Format, Data, Event, Other, All). If you click on [Event] you should see in the properties window (After Update).

Click on the drop down next to it (you may not see the drop down until you click in the box next to it) and select Event Procedure. Finally there is a ... button next to Event Procedure, click that and it will open up the vba code window for that control.

This is where you would start your coding experience.

Everyone's gotta start somewhere so don't worry about being new to it. It don't bite all the time

 

[jdraw]

Harry,

Spend a couple of hours with the material at this site. You just need to get oriented. It's software and it doesn't fall out of the box and talk to you. There are some concepts that you need to grasp, the rest will come with a little trial and error (called experience)

http://www.accessmvp.com/strive4peace/