VBA in Access

[richy]

I'm programming forms using Visual Basic in Access. Is there an event that will fire everytime a record is changed. I thought about updating it if the first name for example changes but what if there are 2 records that have a first name as blank, this wouldn't make any changes.

Hi, you need to be more specific about ur problem. Here what u have stated is not providing the required view. Anyway, basing on what i understand, for a table in the database we recognise the records using a primary key to avoid problems such as what u have stated (2 records with the firstname field as empty). Well a Primary Key cannot be empty and it needs to be unique. So, that should help in keeping track of the records by uniquely identifying them.
Well, if u can be more specific about ur problem then may be u can get n number of replies over here...all the best...vijay

[richy]

how do you fire an event that will check whenever the ID changes then. If the PageUp button is pressed for example, I can't find anyway of firing something as soon as that text box with the ID changes, only if the actual data has been updated (using AfterUpdate).

[jmcswain]

Both of your questions can be handled by Form events; not control events.

Form Before/AfterUpdate fire when a record (not a field) change.

Form OnCurrent fires when the record pointer changes (Page up, for example)

[Edited by jmcswain on 11-06-2000 at 05:25 PM]

[richy]

thanks very much jmcswain. i actually found the OnCurrent function 5 minutes before I had to go home yesterday at 5 o clock after going through practically every feature in the program.
I don't suppose that you know how to change values in a database in this code. I'm browsing one database, but want to edit the values in another, my settings database.

[jmcswain]

something like:


Dim wk As Workspace
Dim db2 As Database

' Return reference to default workspace.
Set wk = DBEngine.Workspaces(0)
' Return reference to Another.mdb.
Set db2 = wk.OpenDatabase("Another.mdb")


should do the trick.

[richy]

rather than changing the database, i just want to change the table, so do i just change the recordset. does the defining of workspaces actually work in access then. i thought it was just in vb.

[jmcswain]

You are correct - just create a new recordset pointing at a different table.

Yep, the workspace thing works in both VB and Access, but I just remembered that that's for DAO only. For ADO, you need to create a new connection object.

[richy]

i'm programming a form which is inside that database with the table in which i want to change. how do i get a handle to the database thats already open on the form. shall i do that using the code that you have previously posted.

[jmcswain]

Dim db As Database

Set db = CurrentDb


is for the current database


Dim rst as Recordset

Set rst = Me.Recordset.Clone


is for the form's table

[richy]

Thank you very much jmcswain. From using your code etc
Me.Form.RecordSource = "tblorg".
just to be awkward :-), i've found out how to change tables, but I want to create a new recordset with the other table in so that the other table is still linked to the form.
for example, my form uses tblsandy but i want to use a lookup table running behind it linked to tblorg. i've also tried doing the
dim db as database, and it tells me that it is invalid.