Results 1 to 9 of 9

Thread: findfirst help

  1. #1
    Join Date
    May 2004
    Posts
    36

    Angry Unanswered: findfirst help

    Hi, I have field "date" in a mainform based on table "dates" on that main form I have a subform based on table "daysofweek" with field "monday". I want to put a button on main form that using VBA searches all the records in table "daysofweek" ( not just the one showing in the subform ) for a match between "date" and "monday".
    Now I know there are other ways of achieving this such as a combobox or whatever but I want to learn this function so I can use it elsewhere as well. Any help would be greatly appreaciated!
    Thanks,
    Raif
    raif@mailblocks.com

  2. #2
    Join Date
    Jun 2004
    Posts
    96
    Hi there,

    if you want to search a table and return a list of records for display, I suggest you use select statements (SQL) and return the result to a list box or combo, depending on your preference.

    eg: listbox (lstResult)
    lstResult.rowsource = "SELECT * FROM table WHERE [table date] = #" & frm.date.value & "# AND [table day] = " & forms!(subform).controls("daysofweek").value & ";"

    I'm typing this from nowhere.. so pls modify accordingly to suit your program..

    hope this helps..

    cyherus
    Last edited by Cyherus; 06-14-04 at 01:02.

  3. #3
    Join Date
    May 2004
    Posts
    36
    Thanks for suggesting this meathod as an alternative. I will play with this meathod as well, however, I'm really trying to get a grip on the findfirst command and how to refer to the paramerters when trying to compare a field in a main form with a field in a subform. Also I am having trouble wrapping my mind around SQL statements in VBA. It seems that there are a number of different syntaxes one can use, for instance with the quotes, brackets and parentheses and it looks like your using the # sign for someing as well. I can't seem to find the system or the syntactic(sp?) rules that would let me do more than just copy peoples code.
    Anyway, thanks for your help
    Raif

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you need the funny punctuation for SQL and for findfirst

    #date#
    'text'
    number

    as in SQL:
    WHERE thisDate = #" & boxOrVarWithADate & "# ORDER BY ...etc
    WHERE thisText = '" & boxOrVarWithTextString & "' ORDER BY ...etc
    WHERE thisNumber = " & boxOrVarWithNumeric & " ORDER BY ...etc

    as in findfirst:
    myRecordset.FindFirst "TextFieldName = '" & boxWithTextString & "'"

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    May 2004
    Posts
    36
    Thank you this certainly clears things up a bit. I have (for now) two more questions. What's with the & signs. Are they just required to enclose all variables or fields sort of like quotes or brackets etc. and secondly how do you refer to specific fields both inside and outside of the current recordset.
    Thanks again for the info
    Raif

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    no no.

    & is the concatenation operator (just a fancy name for joining two bits of text together)

    eg:

    dim myString as string
    myString = "some text"
    'at this stage, the variable holds some text

    'imagine a textbox called anyBox where the user has entered FRIDAY
    myString = myString & anyBox 'it now holds some textFRIDAY

    'for SQL/findfirst, text values need to be quoted, soooooo
    myString = "WHERE textField = anyBox"
    ' CRASH.... anybox !!! not the value in anybox

    myString = "WHERE textField = " & anyBox
    ' CRASH.... WHERE textField = FRIDAY !!! not = 'FRIDAY'

    myString = "WHERE textField = '" & anyBox & "'"
    ' OK.... WHERE textField = 'FRIDAY'

    similarly with the # for dates.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    May 2004
    Posts
    36
    Thank you. I am now clear! Infact I'm so clear that I've run into another problem down the line and since I am lucky enough to have your attention right now perhaps you could help me with this as well.
    I have the tabe dates and daysofweek. there is a rel. btwn dates.week and daysofweek.monday with ref int. enforced.
    when I create new record and add a date in dates.week it creates a corresponding record in daysofweek as you would expect. However, when I do it in code using addnew it creates new record in dates but not in daysofweek . Here is my code

    Dim db As DAO.Database
    Dim rsDOW As DAO.Recordset
    Dim dtSelweek As Date
    Dim rsDates As DAO.Recordset
    Set db = CurrentDb
    Set rsDOW = db.OpenRecordset("select * from daysofweek", dbOpenDynaset)

    Set rsDates = db.OpenRecordset("SELECT * from dates", dbOpenDynaset)


    rsDOW.FindFirst "monday = #" & Me.selweek & "#"



    If rsDOW.NoMatch Then
    With rsDates
    .AddNew
    !Week = Me.selweek
    .Update
    .Bookmark = .LastModified
    End With

    Else
    DoCmd.Beep
    MsgBox "whoo hoo"
    Thanks for your attention
    Raif

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    is it your referential integrity getting in the way?
    do you need something like:

    .AddNew
    !Week = Me.selweek
    !somethingElse = Whatever 'to keep ref int happy
    .Update

    or is it
    !somethingElse = rsDates!Whatever 'to keep ref int happy

    anyhow: it's red-wine and TV time for me

    good luck.


    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Jun 2004
    Posts
    96
    hi, you still have to perform .addnew in your DOW recordset. DB will not create a new record automatically for you basing on referential integrity.. RI only ensures that data are consistant and can be referenced from the foreign table.
    You have to add the record/data in the DOW table first.. then dates table..

    Cyherus

Posting Permissions

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