1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Service Database in access

Discussion in 'Business Applications' started by Damonc, Mar 8, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. Damonc

    Damonc Thread Starter

    Joined:
    May 9, 2001
    Messages:
    644
    Hi.. I've been asked to do up an access database to keep records for a computer workshop.. My knowledge on Access is a tad (ok maybe fairly) rusty..

    Here's what I've got in mind.. (see attachment).. hope that you can read my scribble.

    I'm just not sure on how to get access to actually perform the phone number lookup to see if the customer exists in the database, and also use the customers info to link (or fill) fields in the jobs table.. so basically each time a service job comes in the customer doesn't need to be re-created in the database each time.
     

    Attached Files:

  2. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    Damonw, forgive me if this goes t*tsup – it’s my first attempt at using VBA in access, but i’m sure OBP will be along shortly to put us back on the correct path.

    To do the telephone search, I created an unbound text box and the code for afterupdate {ie. Triggers when you hit [return] or click outside of the textbox} is

    Code:
    [COLOR=black][FONT=Verdana]Private Sub Text9_AfterUpdate()[/FONT][/COLOR]
    [FONT=Verdana][COLOR=black]If (Text9 & vbNullString) = vbNullString Then Exit Sub[/COLOR][/FONT]
    [FONT=Verdana][COLOR=black]   Dim rs As DAO.Recordset[/COLOR][/FONT]
    [FONT=Verdana][COLOR=black]   Set rs = Me.RecordsetClone[/COLOR][/FONT]
    [COLOR=black][FONT=Verdana]   rs.FindFirst "[telephone]=""" & Text9 & """"[/FONT][/COLOR]
    [COLOR=black][FONT=Verdana]   If rs.NoMatch Then[/FONT][/COLOR]
    [FONT=Verdana][COLOR=black]       MsgBox "Sorry, no such record '" & Text9 & "' was found. Create New Customer Record", _[/COLOR][/FONT]
    [FONT=Verdana][COLOR=black]              vbOKOnly + vbInformation[/COLOR][/FONT]
     
    [FONT=Verdana][COLOR=black]       DoCmd.GoToRecord , "", acNewRec[/COLOR][/FONT]
    [FONT=Verdana][COLOR=black]              [Telephone] = Text9[/COLOR][/FONT]
    [FONT=Verdana][COLOR=black]   Else[/COLOR][/FONT]
    [FONT=Verdana][COLOR=black]       Me.Recordset.Bookmark = rs.Bookmark[/COLOR][/FONT]
    [FONT=Verdana][COLOR=black]   End If[/COLOR][/FONT]
    [FONT=Verdana][COLOR=black]   rs.Close[/COLOR][/FONT]
    [FONT=Verdana][COLOR=black]   Text9 = Null[/COLOR][/FONT]
    [COLOR=black][FONT=Verdana]End Sub[/FONT][/COLOR]
    
    See attached...
     

    Attached Files:

  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    DamonW, I have an Access database for Computer Repairs, it is more busines or shop based than workshop, but we could easily adapt it to do what you want. It was written for a couple of other posters on this forum.
    Currently it looks up the Customer name rather than the Phone Number, but we could easily add that.
     
  4. Damonc

    Damonc Thread Starter

    Joined:
    May 9, 2001
    Messages:
    644
    thanks for the code turbodante I'll keep a copy of that for reference or later use...

    OBP - that sounds good..
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    Damon, I have private mailed you my email address, if you contact me I will send you a copy of the database.
     
  6. Damonc

    Damonc Thread Starter

    Joined:
    May 9, 2001
    Messages:
    644
    OBP thanks for sending me the database, I showed it to my friend that wants it but he said it wasn't exactly what he was after..

    So i'm wondering I can head down a slightly different path...

    if data has already been entered into the database, is it possible to run a macro once the user tabs out of say a serial number or surname field, have it find the last record entered matching those details, duplicate it, then clear the entries in the table that you no longer need? - hope that makes sence..
     
  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    Damon, you can do whatever is needed, but your description is not the way that it should be done.
    If a record already exists for a Customer or Serial Number then you should go to that record and Update it if necessary, not duplicate it and then delete it.
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/908563

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice