Solved

Cycle through Access table and retrieve field

Posted on 2014-03-27
6
170 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 book by J.Peter Bruzzese, Microsoft MVP

Are you using Office 365? Trying to set up email signatures but you’re struggling with transport rules and connectors? Let renowned Microsoft MVP J.Peter Bruzzese show you how in this exclusive e-book on Office 365 email signatures. Better yet, it’s 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

Why are Office 365 signatures so complicated?

Trying to setup transport rules for Office 365 email signatures and can’t quite figure it out? Having to test the signature over and over? Make things simple by using Exclaimer Cloud - Signatures for Office 365.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Microsoft OneNote-Office 365 1 34
Excel Type grid in Publisher 1 32
Cloud to Hybrid 4 22
Enable Modern Authentication in Exchange Online 1 7
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
If you don't know how to downgrade, my instructions below should be helpful.
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…
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…

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now