MS Access question

  • Page

    of 1

    Previous Next
  • crashVoodoo 10 Jan 2012 03:20:53 3,821 posts
    Seen 11 hours ago
    Registered 12 years ago
    i'm self teaching MS Access as i've come to the realisation that it will give me more flexibility for the things i need to do at work. Although i think i'm getting the hang of a it, i'm lacking guidance so i shall be posting random questions in here in that hope that anybody with more knowledge can help. apologies if some questions are long...ish

    a Nexus a day keeps the apple away

  • crashVoodoo 10 Jan 2012 03:31:22 3,821 posts
    Seen 11 hours ago
    Registered 12 years ago
    bit long winded for a simple question but bare with me :)

    right, i've sat down and designed a layout before creating and then constructed my DB. All was good and i was using the lookup wizard/table view to link some tables which seem right. however, tonight i've been reading lots of dos and dont's and it appears this is wrong thing to do?

    example

    tblAllocation
    -id#
    -name
    -location
    -email

    tblLocation
    -id (guides recommended deleting this and using the location field alone)
    -location

    apparently I should use a form against tblAllocation. change tblAllocation.location to a combo and set its rowsource as the tblLocation.location

    i've built this and it works but ... isn't this creating repeated duplicated entries eg. Chippingham, instead of a number pointing at Chippingham thus increasing data used ?

    still here, thanks. hope you can shed some light.

    a Nexus a day keeps the apple away

  • Widge Moderator 10 Jan 2012 07:47:31 12,599 posts
    Seen 1 hour ago
    Registered 6 years ago
    From what I see is that you have a table handling all your data entry stuff and one with a definition of locations. Each location has a numerical code but in the form you display the long text. When saving the record to the data entry table you save the numerical code rather than the long text.

    This is how we would have it on our SQL. Table full of Supplier info but each supplier has a solitary code, that is used in any subsequent tables that need to say "this is data belonging to supplier X" like invoice data. This way if you ever need to report on it in some kind of depth and pull in supplier info, you join on code and pull back in the data from table.

    _ _ _

    www.unpaused.co.uk - electronic noise adjective salad

  • crashVoodoo 10 Jan 2012 08:36:00 3,821 posts
    Seen 11 hours ago
    Registered 12 years ago
    Yep, that makes sense to me.
    I was doing this via the table itself using a look up wizard which seems to be frowned up and can cause problems later.  Doing it via a form and doing the look up from the table instead seems to be the done thing but I can't find an explanation why.

    The bit that I was most confused about was the suggestion of removing the Id# field and storing the text string instead.  Maybe I read it wrong or missed something.

    Anyways you've confirmed what I expected should be happening so I'll leave the id# in and change the bound column.

    Cheers

    Edited by crashVoodoo at 08:38:03 10-01-2012

    a Nexus a day keeps the apple away

  • crashVoodoo 12 Jan 2012 22:00:53 3,821 posts
    Seen 11 hours ago
    Registered 12 years ago
    Im back with more ...

    Basically I look after handheld terminals and provide support etc.   I created a really complex spreadsheet about 2 years ago with multiple sheets, macros and forms to manage the stock, arrange repairs etc.
    It works really well but the problem is getting the data back out for reporting or just quick analysis.  I have to create scripts for trawling thru the sheets which frankly is a pain in the ass.  Database queries are a dream in comparison.

    So in the DB i am creating (and learning access at the same time), i have to log them in for testing on tblReturns, which is linked to tblUpdates in a subform so that I can continually add updates.  This is all working perfectly.

    What I now want to do is update the status and allocation of the logged serial# in tblHHTs and also create a record in tblAllocationHistory all from the same form.

    Is it possible?
    Is there a limit to how man tables you can update on one form?

    Edited by crashVoodoo at 22:06:58 12-01-2012

    a Nexus a day keeps the apple away

  • Deleted user 12 January 2012 22:05:06
    It needs to be in a form and not just a query? I can't check without it in front of me but I remember doing it in a form is a bit more tricky than it should be.
  • Widge Moderator 12 Jan 2012 22:14:35 12,599 posts
    Seen 1 hour ago
    Registered 6 years ago
    I think you'd have your form running some vba. I used to put buttons in and have some "on click" command running. So it's be click button, vba runs, vba takes one field on the form and writes to one table, takes the other field on the form and writes somewhere else.



    I forget the syntax but you can usually google it or hit the google groups.



    This is if I understand you right.

    You can do all sorts of stuff with vba, it can understand if you've edited things, you can make drop downs dynamic and related to other drop downs on the form. Slap in subforms that query based on your form data, returning tables of data. All with lots of clever on click, lost focus, gains focus triggers to run your scripts.

    Being versed in Excel VBA you should be halfway there.

    Just ' every step as they are bastards to pick apart later.

    Edited by Widge at 22:19:32 12-01-2012

    _ _ _

    www.unpaused.co.uk - electronic noise adjective salad

  • crashVoodoo 12 Jan 2012 22:35:59 3,821 posts
    Seen 11 hours ago
    Registered 12 years ago
    I was thinking it would be some vba. Possibly on an onchange for the update status and allocation of the hhtserial?

    For the hhtallocationhistory entry i only want the new record created if I actually  add a new record to the returns table so, would 'afterupdate' trigger this ?

    You're right about commenting everything though.  The excel vba stuff I've done is split over 6 or 7 modules plus forms.  I had to structure and overly strictly comment it not just for me but for someone else to walk in and pick it up should I leave.

    Really enjoying this access DB stuff tho, it is so much easier to put forms together with just a simple query .

    Edited by crashVoodoo at 22:41:34 12-01-2012

    a Nexus a day keeps the apple away

  • Widge Moderator 12 Jan 2012 22:41:12 12,599 posts
    Seen 1 hour ago
    Registered 6 years ago
    Ooo now this is where I start to get hazy as it was years ago. I can only suggest experimentation.

    One thing I DID do was remove all record navigation controls from forms so that you actually have press a button to update. That means you are only going to be updating or running vba when you click a save button etc.

    _ _ _

    www.unpaused.co.uk - electronic noise adjective salad

  • Widge Moderator 12 Jan 2012 22:43:37 12,599 posts
    Seen 1 hour ago
    Registered 6 years ago
    You can have something on the returns table that runs after updating or losing focus which checks to see isdirty (I think). It knows then you have edited something.

    _ _ _

    www.unpaused.co.uk - electronic noise adjective salad

  • crashVoodoo 12 Jan 2012 22:48:45 3,821 posts
    Seen 11 hours ago
    Registered 12 years ago
    Widge wrote:
    You can have something on the returns table that runs after updating or losing focus which checks to see isdirty (I think). It knows then you have edited something.
    Nice, Ive seen that one i the form properties, ill take a look at that.  I like the possibility of forcing a click to add the record.  This might be the route I go down

    Cheers widge

    Edited by crashVoodoo at 02:02:18 13-01-2012

    a Nexus a day keeps the apple away

  • crashVoodoo 19 Jan 2012 23:31:33 3,821 posts
    Seen 11 hours ago
    Registered 12 years ago
    Well, I've made some progress on this and I've done it via unbound forms and some nifty vba and SQL strings.  Can't believe how easy it was to make changes with a bit of SQL.  Makes all the silly faffing about coding functions in excel to do the same thing seem stupid.

    Anyhoo, Works a treat.  I've got my code now, just need to make it all purdy and look user friendly :-)

    Cheers guys

    Edited by crashVoodoo at 23:33:24 19-01-2012

    a Nexus a day keeps the apple away

  • crashVoodoo 29 Feb 2012 23:29:31 3,821 posts
    Seen 11 hours ago
    Registered 12 years ago
    Scratch all that .. figured it out. SQL union query.

    Edited by crashVoodoo at 23:31:35 29-02-2012

    a Nexus a day keeps the apple away

  • crashVoodoo 1 Jul 2012 16:01:53 3,821 posts
    Seen 11 hours ago
    Registered 12 years ago
    What would be considered best practice with regards to shared tables ?

    I have a technical support database and have been asked to create one for the transport team for vehicles.  There are a few tables that are going to be shared between them such as managers, engineers etc which currently reside in the technical DB back end.

    Would it make sense to move these to another 'shared' backend and link them into the technical and transport backends or just merge the lot into one large backend and keep the 2 seperate frontends?

    Edited by crashVoodoo at 16:03:36 01-07-2012

    a Nexus a day keeps the apple away

  • Page

    of 1

    Previous Next
Log in or register to reply