Add record from another table with one-to-one relationship 1

[lacasa]

I have one master table with 2000 records containing name and address info. Some of these people are board members. Separate information is kept if they are board members. This extra information should not be kept in the main master table. So I have a master table and a board member table. They have a one-to-one relationship. They are linked with an ID number coming from master table.

If a person becomes a board member I want to be able to automate the process by opening up the master table and selecting the person and clicking on a command button and then that persons ID will be added to the board table. I know I could simply remember the ID number and manually add a record to the board table.

Can I do the above with a macro or do I need VBA. How do I do it? Or am I approaching this in the wrong way?

 

[LittleSmudge]

Very simple to do it in VB code.
( In a macro ? arrrrh Who'd even consider it thesedays ? )



Assuming you have a form, bound to the main table and containing a control called RecordId that is bound to the Id field in the table ( and contains the linking info for the BoardMember table )

Have the button on the form and then in the OnClick event

Open a RecordSet 'rst' using
"SELECT * FROM tblBoardMembers WHERE False"


rst.AddNew
rst!BoardId = RecordId
rst!fieldNext = Whatever ' Fill in any other info the db knows at this time - Eg
rst!PromotedToBoard = Date()
etc ..

rst.Update
rst.Close


The actual code for opening the recordset depends on which version of Access you are running.

If you need help with the detail you need to let us know what version.


'ope-that'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[lacasa]

Thanks. Time to learn VBA. From your reply and what I found under the help files I put this together and it works.

Private Sub AddBoard_Click()
Dim lacasa1 As DAO.Database
Dim rst As DAO.Recordset

Set lacasa1 = CurrentDb


Set rst = lacasa1.OpenRecordset("SELECT * FROM Board WHERE False&quot

rst.AddNew
rst!BoardID = ID
rst.Update
rst.Close
End Sub


Do I know what I'm doing?????? No. I am not sure if I needed the Dim statements. Or the Set lacasa1 = currentDb statement. As you can see from OpenRecordset I am using Access 2000. Is this code still valid with Access XP? And what is the exclamation mark before BoardID?

What do recommend as a book or learning tool for VBA?

Thanks for your help.

 

[LittleSmudge]

What I see from the "OpenRecordSet" is that you are using DAO.

The question that leaps to my mind is WHY, oh WHY are you using DAO if you have Access 2000?


However, I'll cover that in a minute.
Firstly, If the second line on you code page says
OPTION EXPLICIT
then you DO need the DIM statements.

If the second line doesn't say Option Explicit - IT SHOULD
So fix that and keep the Dim statements.
To fix it permanently go to the code window and from the Tools Menu select Options and in the Editor tab tick the Require Variable Declaration option.

And YES you do need the SET line



Now Then.
If you are using A2k then you have ADO available to you as standard.

Opening a recordset in ADO has a few more lines - but it is not diffecult.

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst.ActiveConnection = CurrentProject.Connection

rst.Open "SELECT * FROM Board WHERE False"

'then rest continues as before
'but when editing existing records in ADO you don't need rst.Edit - it's automatically assumed.



rst.ActiveConnection is the equivalent of the
Set lacasa1 = CurrentDb
Adjusting rst.CursorType and rst.LockType give you greater flexibility and control.


Once you have the code converted, go into the code window, Tools menu, References and UNtick the DAO 3.6 library line. as you don't need it any more.
Your database will then load quicker each time you use it.



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.