Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2003
    Location
    China
    Posts
    54

    Unanswered: AddNew recordset in DAO - Help!

    Hi guys and gals,

    I am pretty new to VB, and am trying to create a search that retrieves the PersonnelID field from various tables and adds them to a Search table.

    I can't get new records added to the search table using the VB on a button below:
    -----------------------------------------------------------------
    'Search based on the strings we have built
    Set db = CurrentDb
    Set rcdFinal = db.OpenRecordset("SELECT Search.ID FROM Search", dbOpenDynaset)

    'Do any people Personnel records satisfy the WHERE clauses?
    Set rcdPer = db.OpenRecordset("SELECT DISTINCTROW Personnel.PersonnelID, Personnel.GivenNames FROM Personnel WHERE " & strFilterPers, dbOpenDynaset)
    rcdPer.MoveFirst
    Do While Not rcdPer.EOF
    rcdFinal.AddNew
    rcdFinal!ID = rcdPer!PersonnelID
    rcdPer.MoveNext
    Loop
    -----------------------------------------------------------------
    I have also attached the full VB script for those who are interested.

    Any help would be appreciated.

    Thanks,

    Richard.
    Attached Files Attached Files
    Last edited by Trumpet; 07-03-04 at 23:29. Reason: forgot to attach attachment

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    At a quick glance, you're missing the Update method.

    Do While Not rcdPer.EOF
    rcdFinal.AddNew
    rcdFinal!ID = rcdPer!PersonnelID
    rcdFinal.Update
    rcdPer.MoveNext
    Loop

    Also, consider using rcdFinal("ID") instead of rcdFinal!ID

    As you get better with coding, you'll find it more useful. The value in the () can either be a string that explicitly names the field, or an integer, which reference the field by it's index (field indexes in DAO recordsets start at 0).

    So, if you have 15 fields, you can make a loop and use a variable instead of having to type out each field name.

    Hae fun

  3. #3
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    tcace,

    Thanks for that - now the Search table updates. There is an abnormality though. When I click the search button it asks for a value for the Table!Search!ID field. Doesn't seem to matter what I enter as the results seem to be stored in the Search table correctly.

    Any thoughts?

    Richard.
    Last edited by Trumpet; 07-06-04 at 01:21.

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    hmmm, not sure.
    Check the table, and make sure none of the fields are set to "Required = Yes"

  5. #5
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Just a question:

    Why are you first query'ing the personel table with a search string then storing the IDs to get the records from the IDS from the personel table?

    DO you still get it ? I dont imagine so, but thats what you are doing...

    Why not simply use the strFilterPers direct on the form?
    DoCmd.OpenForm FormName:="PersonSearchList", WhereCondition:=strFilterPers


    May i also suggest you use Me. instead of Me! .... try it you will love it....
    Also may i suggest changing your code a little to make it more readable.... Something like so:
    Code:
      strFilterPers = "[GivenNames]  LIKE " & Chr$(34) & "*" & Me.NameKey & "*" & Chr$(34) & _ 
                  " OR [Surname]     LIKE " & Chr$(34) & "*" & Me.NameKey & "*" & Chr$(34) & _ 
                  " OR [NameChinese] LIKE " & Chr$(34) & "*" & Me.NameKey & "*" & Chr$(34)
    I only mean to help....

    Regardz

  6. #6
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    tcace,

    Thanks for all your help. I solved the problem. There was some code later in the sub that openned the search results form - this was too complex so I simplified it and made the 'Search' table part of the search results form's query. Problem solved.

    namliam,

    The code you have seen is the basis for a search that will eventually lookup 1. the parent table (Personnel) and 2. 3 seperate child tables (WorkExp, Education, and Language). I found using your method worked with 1 parent and 1 child only.

    I confess to be new to VB, but the best way I have found to resolve the search of 1 parent to multiple child tables was to have the parent's ID stored in this 'Search' table and used to create the search results form. Also, I hope to be able to 'weight' search results later (as I am using multiple search criteria as well), so that if an ID is recalled more than once a counter will increase - the final results being sorted by the counter.

    I haven't been able to find anything about this sort of searching - so if you think there are better ways I would be greatful to hear your thoughts.

    p.s. Why use "Me." instead of "Me!" ?

    Cheers all,

    Richard.

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    To answer the "Me" question, VB is "Object Oriented Programming". Things are "Objects" or "Containers".

    Each Object has properties, methods and actions. A container has these as well, but also contains Objects of it's own (therefore, most things are actually Containers).

    You will also hear about "Collections" from time to time. A collection is a set of matching objects. A form, for example, has a collection of controls - each control on the form is in that collection.

    For example, each Form is a Container, that is to say, it is an Object and also has it's own set of Objects. The form has "Properties" such as "FormName", and "Actions" such as "Requery" and "Methods" which include arguments. It also has objects, namely the controls you place on it.

    Each control is an object. A Text Box is an Object that has it's own assortment of properties, methods and actions.

    When you type "Me" you are referencing the container you are in - generally this is used in code behind a form. It's nice, becasue you can move code from one form to another easily. Anyway, when you use "Me", it's the same as explicitly referencing the Form Object, namely Forms!YourForm!YourControl.

    So, why the period instead of the exclamation point you ask? Open a form in design veiw and switch into code view. Now, type Me. and a little "combo box list" appears with all of the objects, methods, properties and actions available for the form. You can type the first few letters, page up and down, arrow up and down and tab when you find a value you like. Choose a control name on the form and hit the period again and a new list apprears - the objects, methods, properties and actions available for the control. This happens when you follow any object directly with a period.

    It makes coding more accurate and faster! For more detail, I suggest visiting a good book store and browsing the computer books until you find one appropiate for your level (perhaps a bit above your level so it remains useful for a while!). You can also search the help file for "Help on VB"

    Have fun!

  8. #8
    Join Date
    Apr 2003
    Location
    China
    Posts
    54
    tcace,

    Cheers for that... I will give it a go.

    Richard.

Posting Permissions

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