Need a little help with this code!

[TheMadRonin]

Hi folks!

I will confess right here up front that when it comes to VBA code I am a total novice and still have a great deal to learn so I am appealing to the good nature and superior expertise of you wonderful people to help me achieve my goal!

Please note I have searched high and low for an answer here on the boards but cannot seem to find anything that helps out!

Ok with that in mind, what I am attempting to do here is this... when the user clicks a button on the form, the account numbers of all linked accounts will pop up in a nice friendly msgbox, for example:

AcNo LinkNo
1234 1
2345 1
3456 1
4567
5789

So when the user looking at account 1234 clicks the button the message box will return a brief intro and the the values 2345 & 3456.

The code in itself works exactly as I want it to except for the fact that due to using DLOOKUPthe only value it will return is the first value in the link set, ie 1234 regardles of which account is being viewed.

So would any of you delightful people know where I am going wrong (apart from with using DLOOKUP of course) and what I can do to remedy it?

Many thanks is advance,

TheMadRonin.

[FONT=Courier New]Private Sub LinkFilter_Click()[/FONT]
[FONT=Courier New]   Dim varX As Variant[/FONT]
[FONT=Courier New]   If IsNull([LinkNumber]) Then[/FONT]
[FONT=Courier New]       MsgBox "This trust is not linked to any other trusts."[/FONT]
[FONT=Courier New]   Else[/FONT]
[FONT=Courier New]       varX = DLookup([IMSNumber], "tblregister20082009", [LinkNumber] = Me!LinkNumber)[/FONT]
[FONT=Courier New]       If MsgBox("This trust is linked to the following trusts:" & vbCrLf & vbCrLf & varX & vbCrLf & vbCrLf & "Do you wish to filter the register to work on these entries only?", vbQuestion + vbYesNo + vbDefaultButton2, "View Linked Trusts") = vbYes Then[/FONT]
[FONT=Courier New]           Dim stDocName As String[/FONT]
[FONT=Courier New]           Dim stLinkCriteria As String[/FONT]
 
[FONT=Courier New]           stDocName = "frmReturns20082009"[/FONT]
[FONT=Courier New]           stLinkCriteria = "[LinkNumber]=" & Me![LinkNumber][/FONT]
[FONT=Courier New]           DoCmd.OpenForm stDocName, , , stLinkCriteria[/FONT]
[FONT=Courier New]           Me!LinkFilter.Visible = False[/FONT]
[FONT=Courier New]           Me!ResetFilter.Visible = True[/FONT]
[FONT=Courier New]       Else[/FONT]
[FONT=Courier New]           End[/FONT]
[FONT=Courier New]       End If[/FONT]
[FONT=Courier New]   End If[/FONT]
[FONT=Courier New]End Sub[/FONT]

 

[DCrake]

Concept correct, methodology incorrect.

For start DLookup only returns one value and if you returned more than one value to the message box how would the user distinguish between different accounts?

My approach would be first to return a recordset based on the id of the link.
Then I would count how many accounts share this link.
Using a Select Case statement would let me go down different routes:-

Records found

0 - No matching accounts found
1 - only one account no need to ask which one to view
1+ more than one - ask user if they want to choose an account

If user selects to select a specific account then I would open up a simple popup form that has either a listbox or a combo box that contains all the known account for that link, with a prompt to select one. Having selected on they click a button which opens the desired form with the correct link criteria. I would still have the select form open as they may need to visit more than one account for that link and do not want to repeat the whole process over and over again.

David

 

[TheMadRonin]

Thank you very much for taking the time to read and subsequently reply to my predicament but I am afraid that your reply and subsequent explaination has just flown so far over my head that it's in orbit!

I wouldn't have a clue where to begin in trying what you have suggested!

 

[DCrake]

I take it that on a scale of 1 - 10 your Access skills are somewhat limited. The normal genre here is "give a man a fish and he will eat today. Show him how to fish and he will never go hungry" Time to start fishing.

David

 

[TheMadRonin]

Again thankyou for taking the time to reply, it is much appreciated.

My access skills are not too shabby in and of themselves, it is my VBA skills which are the weak link. The forum mantra is a good one which I whole heartedly agree with and I am not looking for somone to do my job for me, it is more a case of never having seen this "Select Case" beast before, that in combination with the rather lack-lustre help facilty in Access is making understanding it somewhat more difficult... but I shall however soldier on and crack this mystery in time.

 

[DCrake]

A Select Case Statement is a more refinded way of asking questions of the data. Normally one question equals one answer. however depending on the question asked a different answer is produced. So if I was to ask you how many miles do you live away from me and based on that answer I would take the most appropriate mode of transport.

So my question is: how far away do you live?
the answer is Distance

However I want to be able to predict the possible modes of transport available to me.

Select Case Distance
   Case < 1 :I will walk there
   Case < 5 : I will cycle there
   Case < 10: I will get the bus
   Case < 50 :I will drive
   Case Else:Too far away, stopping here.
End Select

Now here I have covered all the bases using the final Case Else line, so no matter what the answer was for Distance I can give you a valid response.

Hope the logic is more understandable.

David

 

[TheMadRonin]

That actually helps explain the logic behind it quite nicely, much better than the what Access help files provide, now I just need to get my head around these "recordset" shenanigans and I might just be able to get this problem licked... *fingers crossed*

Once again thanks for the assistance.

 

[DCrake]

An easier way avoiding recordsets for the time being is to use a DCount instead o DLoopkup. Used in much the same way but instead of providng you with a value from a field it will simply tell you how many it has found. Therefore

DCount("*","YourTableOrQuery","FieldInTableOrQuery equals Your condition")

EG

Dim Howmany as integer
Howmany = Nz(DCount("*","Products","Price < 50"),0)

Select Case Howmany
   Case 0
   Case 1
   Case > 1
End Select

David

 

[TheMadRonin]

Thanks again DCrake for that example code, again helps me to understand how that particular function works, but as there is a field within the database which shows the total number of linked entries it would be somewhat redundant in application.

I have however been doing a little digging on the whole recordset concept you directed me towards and have made adjustments to my code accordingly. I have encountered a level of success with it but also a modicome of failure too.

Private Sub LinkFilter_Click()
    Dim stDocName As String, stLinkCriteria As String
    Dim stLinkList As String, stSQL As String
    Dim db As DAO.Database, rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("qryreturns20082009", dbOpenDynaset)
 
    stDocName = "frmReturns20082009"
    stLinkCriteria = "[LinkNumber]=" & Me![LinkNumber]
    stLinkList = ""
 
    rs.Filter = "LinkNumber=" & Me!LinkNumber
    rs.MoveFirst
    Do
        stLinkList = stLinkList & rs.Fields("IMSNumber") & vbCrLf
        rs.MoveNext
    Loop Until rs.EOF
    If IsNull([LinkNumber]) Then
        MsgBox "This trust is not linked to any other trusts.", , "View Linked Trusts"
    Else
        If MsgBox("This trust is linked to the following trusts:" & vbCrLf & vbCrLf & stLinkList & vbCrLf & vbCrLf & "Do you wish to filter the register to work on these entries only?", vbQuestion + vbYesNo + vbDefaultButton2, "View Linked Trusts") = vbYes Then
            DoCmd.OpenForm stDocName, , , stLinkCriteria
            Me!LinkFilter.Visible = False
            Me!ResetFilter.Visible = True
        Else
            End
        End If
    End If
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

This "in concept" does what I need it do to, unfortunately in practice it falls a little short in that it is returning all values on the recordset not the ones I want to filter out. For some reason I just can't seem to fathom my 'rs.filter' arguement doesn't seem to be doing the job I thought it would.

I also tried an alternate method of filtering with the following but this just kept throwing up the following error:
"Run-time error '3061': Too few parameters. Expected 1."

stSQL = "SELECT * FROM qryreturns20082009 WHERE [linknumber] = me!linknumber"
Set rs = db.OpenRecordset(stSQL)

I feel I am in the right area with where I am going but need to be nudged back on course.

Any help would be greatly appreciated, thanks in advance.

TheMadRonin

 

[DCrake]

You are nearly there

stSQL = "SELECT * FROM qryreturns20082009 WHERE [linknumber] = me!linknumber"

Should read

stSQL = "SELECT * FROM qryreturns20082009 WHERE [linknumber] = " & Me.linknumber

What was happening was that you were not identifying what the link number was. By ending the first part of the string (after the equals sign) and concatenating the actual linknumber you have entered on the form it can now understand the syntax.

Things to bear in mind:

If the criteria is a string then it need wrapping in quotes.
If it is a numeric value then it does not.
If it is a date it needs wrapping in #'s

David

 

[TheMadRonin]

Aha! That has done the trick nicely, thankyou very much for your continued assistance on this DCrake, very much appreciated.

Thanks for taking the time to explain it all through, has helped me to understand it and notes have duely been taken for future reference.

Many thanks,

TheMadRonin

 

[DCrake]

Glad to see you are gleaning knowledge from this forum and assisting you with your projects. Best of Luck.

David