Consulting

Results 1 to 11 of 11

Thread: New Access 2016 User: Trying to link tables and pull data throuhg to a form

  1. #1
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    142
    Location

    New Access 2016 User: Trying to link tables and pull data throuhg to a form

    Hi
    Only just learning Access 2016 and setting up a first basicdatabase at work to play / learn but stuck already and would appreciate anypointers please.
    There are only 2 tables so far Requests & GP List. User would enter data to the Requests tablevia a form also called Requests.
    Both tables have a field called PCode and I am trying to automaticallypopulate part of the form (from the GP List) after a PCode is entered.
    User enter a data into a field called PCode on the requestsform and then I want Fields called Practice Name & Telephone to pull throughfrom the GP list table
    I think I needed toset up a relationship and have done (screenshot) and then trying to code an after-updateevent but cannot get anything to work! Can anyone give me any pointersplease? I have tried some code from theinternet but no joy and not sure if the relationship is necessary or set upcorrectly


  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I don't see a screenshot, perhaps it did not upload.
    First of all you should definitely have a Relationship set up, but that assumes that the data needs to actually be in a separate table.
    The PCode and the GP List sounds like it could all bein one table.
    Normally you need a second table if there is more than one record for each record in the main table.
    ie one doctor, lots of patients.
    One Surgery lots of Doctors.
    If the tables are required and are set up correctly you do not need any VBA code to do what you want.
    Ideally you would not have the user enter a PCode, you would have them select one from a Combo Drop down list box, as it is more accurate and faster than entering it.
    Can you upload a zipped copy of the database with some dummy data in it?
    I forgot the database woudl need to be in Access 2007 format for me to open it.

  3. #3
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    142
    Location

    Thanks & example attached

    Hi OBP and thank you for helping.

    As I said I am new to Access and tying to crate this tool for work as way of trying to get to grips with Access. I think I et up the relationship but I think it should Many to Many as any request could have any PCode. There are around 300 doctors in the list which is why I went for separate table (to many for drop won list) an the Pcode is usually provided at the point of enquiry so typing it in and pulling everything else over would be easier for users.

    It is very basic first one, just trying to get to grips with some basics (date formatting, pulling data into forms, the history etc) which I could then use for other projects as I learn more.

    I have attached a zipped example for you to see what I am tying to do.

    Thank you again

    Mykal
    Attached Files Attached Files

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, that loaded up OK and I have opened the form, so what you want the form to do, ie just populate a couple of fields on the form would require VBA.
    But that goes against the reason for Relational databases, which only has the data stored once and is then referred to.
    What you would normally do is have the Sub Table as a Sub Form with the data that you need displayed.
    The quantity of doctors is not a problem as when using a Combo as you type in the name or Pcode it jumps to those matching.

  5. #5
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    This is how I would do it.
    By the way compare the size of my version to yours, you need to Compact & Repair the database after doing a lot of Design and Development work on it as it gets a lot of "bloat".
    Attached Files Attached Files

  6. #6
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    142
    Location
    Thank you very much for your help and advice - I would never have known about the Compact & Repair either. Really appreciate your help OBP

    Mykal

  7. #7
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Good, let me know if you need anything else.

  8. #8
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    142
    Location
    Hi OBP.

    Ae you able to help with a print command please?

    The database you helped with has a notes history field whichis updated after every new note is added. I have added a CMD button below it (PRINT HISTORY) and want it to printout only the history field when pressed (NotesHistory).
    I have tried me.NotesHistory.Print and google but cannot getit to work.
    Thank you
    Mykal

  9. #9
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    You should create a query with just that field in and then use that as the basis for a Report.

  10. #10
    VBAX Contributor
    Joined
    Jul 2011
    Location
    Manchester
    Posts
    142
    Location
    Thank you i will give it a go :-) Mykal

  11. #11
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    If you need to restrict the report to the record on the form you can do so by setting the Query NotesHistory Criteria to
    Forms![formname]![NotesHistory]
    where formname is the actual name of your form.

Posting Permissions

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