Solved

Cycle through Access table and retrieve field

Posted on 2014-03-27
6
180 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

How to resolve IMCEAEX NDRs in Exchange or Exchange Online related to invalid X500 addresses.
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.
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…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

717 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