Solved

Cycle through Access table and retrieve field

Posted on 2014-03-27
6
175 Views
Last Modified: 2014-04-15
I have a Microsoft access database (2007) which has a contacts table containing company addresses.
To help stop users adding duplicates, one of the fields I was checking was postcode (zipcode). I am currently using Dcount to see if there are any duplicates, a msgbox alerts user that there is x number of Companies with that postcode.

Is it possible to extract the Company names from database where the postcode = 'postcode entered on userform' so the msgbox can advise a list of companies in the database with same postcode.

The user would then check if the new company being added has already been added by checking the list (maybe company name spelt a little differently)
0
Comment
Question by:foxpc123
  • 3
  • 2
6 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 39960901
Try this:

Sub Demo()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Select * FROM tblYourTable WHERE postcode =" & Me.txtpostcode)

    MsgBox rs!postcode
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Sub

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 39961865
You could most easily create a multi-column unique index on the contacts table.  For example, you could make company ID (?name?) and address type a unique tuple.  If the user tries to add a second address type for a company, the Insert will raise a trappable duplicate key error.
0
 
LVL 3

Author Comment

by:foxpc123
ID: 39962167
MacroShadow, that only seems to pick up the first instance of the postcode.

I tried code below also to give Business name, but only first business shown in msgbox:

    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Select [Business name] FROM [tbl - Contact Details] WHERE [Post Code]='" & Me.Post_Code & "'")


    
MsgBox (rs![Business name])

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 27

Assisted Solution

by:MacroShadow
MacroShadow earned 300 total points
ID: 39964131
Of course! You're not supposed to have duplicates.

Anyway, if the is what you want try this:
Sub Demo()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Select [Business name] FROM [tbl - Contact Details] WHERE [Post Code]='" & Me.Post_Code & "'")

    rs.MoveLast
    rs.MoveFirst
    Do Until rs.EOF
        MsgBox (rs![Business name])
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing

End Sub

Open in new window

0
 
LVL 3

Accepted Solution

by:
foxpc123 earned 0 total points
ID: 39992524
This was the code I ended up using

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Select [Business name] FROM [tbl - Contact Details] WHERE [Post Code]='" & Me.Post_Code & "'")
    

    rs.MoveLast
    rs.MoveFirst
    Do Until rs.EOF
     Companylist = Companylist & vbCrLf & (rs![Business name])
        rs.MoveNext
    Loop

thisclient = DLookup("[Business name]", "[tbl - Contact details]", "[Post Code] = '" & Me.Post_Code & "'")


      Response = MsgBox("Postcode " & Me.Post_Code & " already exists in database for:-" & vbCrLf & Companylist & vbCrLf & vbCrLf & "Do you want to continue Adding this postcode ?", vbYesNo + vbQuestion, "Confirm")
        rs.Close
    Set rs = Nothing
    Set db = Nothing

Open in new window


Thanks for the help
0
 
LVL 3

Author Closing Comment

by:foxpc123
ID: 40001120
Final solution gives one alert box, with list of companies with added postcode.

Can have duplicate postcodes, as in UK postcode is not unique to one building.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Veeam Backup & Replication has added a new integration – Veeam Backup for Microsoft Office 365.  In this blog, we will discuss how you can benefit from Office 365 email backup with the Veeam’s new product and try to shed some light on the needs and …
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question