Solved

Cycle through Access table and retrieve field

Posted on 2014-03-27
6
172 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This is my first article on Expert Exchange on the Manual Method of Exporting Office 365 Mailboxes to PST format by using the eDiscovery mechanism of Office. Hope you will enjoy the article.
Microsoft Office Picture Manager has a Picture Shortcuts pane that shows a list with the Recently Browsed folders. While creating my video Micro Tutorial here at Experts Exchange showing How to Install Microsoft Office Picture Manager in Office 2013…
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

776 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