Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2001
    Posts
    175

    Unanswered: Loop not working

    Why isn't my code below working? I am trying to look up a table and populate the form with the results but all I am getting is only the first record and in the table there are two records that match the criteria.

    Please help!!!


    Dim varz As Variant
    Dim AceDbs As Database
    Dim rst As DAO.Recordset
    Dim endoffile As Boolean

    endoffile = False
    Set AceDbs = CurrentDb
    Set rst = AceDbs.OpenRecordset("tblpolperils")
    With rst
    Do While endoffile = False
    varz = DLookup("[code]", "tblpolperils", "[invoiceno] = " & Text20)
    Me!code = varz
    .MoveNext
    endoffile = (.EOF = True)
    Loop
    End With

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try:

    Dim varz As Variant
    Dim AceDbs As DAO.Database
    Dim rst As DAO.Recordset

    Set AceDbs = CurrentDb
    Set rst = AceDbs.OpenRecordset("tblpolperils")
    With rst
    Do While not .EOF
    varz = DLookup("[code]", "tblpolperils", "[invoiceno] = " & Text20)
    !code = varz
    .MoveNext
    Loop
    End With

    can't guarantee that
    varz = DLookup("[code]", "tblpolperils", "[invoiceno] = " & Text20)
    flies 'cos i don't know your details: possible variant is:
    varz = DLookup("[code]", "tblpolperils", "[invoiceno] = " & Text20.value)

    either way, the loop now loops!

    izy

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    it's raining here, so another thought: rst is looping (not DLookup).

    you are going to set every [code] in tblpolperils to whatever value your DLookup returns... i doubt you want this, but if you do AND you don't want to use an update query, at least store the DLookup value in a var before you start. Domain aggregate functions are really nice but they are slow enough that you don't want to repeat them if you can help it.

    if your gameplan is to find all [invoiceno] = Text20.value and do something to these records, you either have to inspect each record as you loop through rst
    if !invoiceno = Text20.value then etc

    or use find/seek

    or, most efficiently, base your rst on some SQL e.g.
    Dim varz As Variant 'why the horrible variant by the way?
    Dim AceDbs As DAO.Database
    Dim rst As DAO.Recordset
    dim mySQL as string
    mySQL = "SELECT [code] FROM tblpolperils "
    mySQL = mySQL & " WHERE invoiceno = '" & Text20.value & "';"
    Set AceDbs = CurrentDb
    Set rst = AceDbs.OpenRecordset(mySQL)
    With rst
    Do While not .EOF 'you are now looping through records with invoiceno = Text20.value
    ' do something --- i can't work out what it is you want to happen
    .MoveNext
    Loop
    End With

    izy
    Last edited by izyrider; 06-14-03 at 15:48.

  4. #4
    Join Date
    Sep 2001
    Posts
    175
    Thanks - will give it a try

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    izy has the best solution

    depending on the recorset size
    you can also use an if then statement
    dim x as variant
    x = 'whatever value you want or a value from a control


    if rst!invoicenumber = x then
    'do whatever
    else
    'do nothing
    end if
    rst.movenext
    loop

  6. #6
    Join Date
    Sep 2001
    Posts
    175
    I am still having problems...

    Let me try to explain what I am trying to accomplish in more detail. Initially I have a continuous form with a combo box where the user selects codes (this can be say 1 to 10 codes from the list) so on the form I have the following fields:

    code
    codename
    (both populated from the combo box)

    What I want to do is not to have the user enter the information in this form again but to query the table (tblpolperils) using the invoiceno as the criteria and populate the form automatically so that the user can then add or delete codes. I think your second option is what I am looking for but I am getting a 'mismatch' error at the line Set rst = AceDbs.OpenRecordset(mySQL).

    I hope you understand a little better what I am trying to achieve and IF IT IS POSSIBLE.

  7. #7
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    datatype mismatch error?

  8. #8
    Join Date
    Sep 2001
    Posts
    175
    I am getting:

    Data type mismatch in criteria expression

  9. #9
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    your criteria is prbbably being evauluated as a string ie

    although you the criteria might be 2
    the field or control on the form maybe a string

    if this is the case use the cint() function or the reverse
    cstrng()


    does that make sense?

    whenever this happens to me I am using an inputbox or a field on an unbound control.

    HTH

  10. #10
    Join Date
    Sep 2001
    Posts
    175
    I don't quite understand but I will give it a try

  11. #11
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    varz = DLookup("[code]", "tblpolperils", "[invoiceno] = " & Text20)

    for instance [invoiceno] = & " & text20

    is text20 a string or number - check the format of the control or the datatype in the underlying recordsource

    it looks like it has been concantonated in as text

    and invoice number may be a number

    also in domain functions I use the full literal of the object

    invoice = forms!fromname!fieldname

    that has saved me much debugging in the past

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •