Results 1 to 11 of 11

Thread: VBA in Access

  1. #1

    Thread Starter
    Hyperactive Member richy's Avatar
    Join Date
    Jan 1999
    Location
    Liverpool, England
    Posts
    353
    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.

  2. #2
    Guest
    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

  3. #3

    Thread Starter
    Hyperactive Member richy's Avatar
    Join Date
    Jan 1999
    Location
    Liverpool, England
    Posts
    353
    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).

  4. #4
    Hyperactive Member
    Join Date
    Oct 2000
    Posts
    400
    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]

  5. #5

    Thread Starter
    Hyperactive Member richy's Avatar
    Join Date
    Jan 1999
    Location
    Liverpool, England
    Posts
    353
    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.

  6. #6
    Hyperactive Member
    Join Date
    Oct 2000
    Posts
    400
    something like:

    Code:
    	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.

  7. #7

    Thread Starter
    Hyperactive Member richy's Avatar
    Join Date
    Jan 1999
    Location
    Liverpool, England
    Posts
    353
    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.

  8. #8
    Hyperactive Member
    Join Date
    Oct 2000
    Posts
    400
    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.

  9. #9

    Thread Starter
    Hyperactive Member richy's Avatar
    Join Date
    Jan 1999
    Location
    Liverpool, England
    Posts
    353
    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.

  10. #10
    Hyperactive Member
    Join Date
    Oct 2000
    Posts
    400
    Code:
    Dim db As Database
    
    Set db = CurrentDb
    is for the current database

    Code:
    Dim rst as Recordset
    
    Set rst = Me.Recordset.Clone
    is for the form's table


  11. #11

    Thread Starter
    Hyperactive Member richy's Avatar
    Join Date
    Jan 1999
    Location
    Liverpool, England
    Posts
    353
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width