Results 1 to 4 of 4
  1. #1
    coffee4kepi is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    2

    Code setting content of Field with ComboBox lookup

    Hello everyone,

    I am new to this forum and MSAccess but I would like to ask you some help in understanding what I am missing in my code.


    I have designed a form with unbound controls, in order to know exactly which messages/actions are done and when. So I have this button to save the data stored in the form in the designed table. In such a table I have a field, called "CausaRicovero", which is set to gather its allowed data from an other table and the field's lookup is set as combobox (more choices are allowed). Now, when in VBA I am trying to initialize the newly created record with "default" values, this field returns a run-time error 64224: "Method 'Collect' of object 'Recordset2' failed" . The code is as follows:

    Code:
    Dim Records As Recordset
    
    If RecordNum = DCount("[Codice]", "DB Accoglienza") + 1 Then
        Set Records = CurrentDb.OpenRecordset("DB Accoglienza")
        Records.AddNew
        Records![Codice] = 5555
        Records![NomeSpecie] = "Piccione"
        Records![DataAmmissione] = 11 / 11 / 1911
        Records![CausaRicovero] = "Impatto"
        Records![ProvinciaRitrovamento] = "Sconosciuta"
        Records![TipologiaIDRitrovamento] = "Privato"
        Records![NomeCognomeIDRitrovamento] = "Sconosciuto"
        Records![ProvinciaIDRitrovamento] = "Sconosciuta"
        Records![TipologiaIDConsegnatario] = "Privato"
        Records![NomeCognomeIDConsegnatario] = "Sconosciuto"
        Records![ProvinciaIDConsegnatario] = "Sconosciuta"
        Records.Update
        Records.Close
    End If
    I do not understand why I get this error, because other fields have similar design, although as listbox and not combobox.

    Thanks in advance,

    Aldo

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    which is set to gather its allowed data from an other table and the field's lookup is set as combobox (more choices are allowed)
    ...
    field returns a run-time error 64224: "Method 'Collect' of object 'Recordset2' failed"
    sounds like you have set the field up as a multivalue field (which along with the attachment field uses recordset2), is this the case? If so, why?

    Not sure why you are using an unbound form? what are the perceived benefits for you? It is much more difficult and more work than using a bound form

    Also, not sure why you don't just set up the defaults in the table?

    And I would expect your code to enter the wrong value here

    Records![DataAmmissione] = 11 / 11 / 1911

    implication is this field is a date datatype and 11/11/1911 (1 divided by 1911) will return something like 0.000523286 which equates to 5 seconds

  3. #3
    coffee4kepi is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    2
    Dear Ajax, thanks for your quick reply.

    sounds like you have set the field up as a multivalue field (which along with the attachment field uses recordset2), is this the case? If so, why?
    In fact it is, indeed. I had to do it because some users can be allowed to fill the record directly in the table format, not just trough the form, so I have to keep the same input restrictions. The point is I guessed that, independently from the field being multivalue of not, the assignment code would be the same as Field = Content. So is there a way to do it?

    Also, not sure why you don't just set up the defaults in the table?
    Because if the user fills directly the table would have to remember to change then the default values, which is something I cannot guarantee (or easily do it).

    Not sure why you are using an unbound form? what are the perceived benefits for you? It is much more difficult and more work than using a bound form
    I know that bound controls are much easier to handle, but for example the biggest drawback to me is that the validation is done just after clicking out of the bound control box and that requires a user that can handle solving error popups coming out, which is something I know it is not the case

    Aldo

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Multivalue fields have an underlying recordset2 datatype. You will need to google 'vba access recordset2' or similar to find out how to populate them. I would imaging you will need to create a recordset2 object, open it and populate it. Personally I avoid them because of their limitations.

    don't understand you response to using defaults in the table - a default is a default, why would it change?

    re bound controls - you would have code to handle errors - users should never see the code or tables/queries directly. And you will still potentially get errors with unbound controls which will require the same code to handle them

    good luck with your project

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 03-02-2016, 09:17 PM
  2. Replies: 8
    Last Post: 01-06-2016, 02:52 PM
  3. Combobox setting other combobox values problem
    By maxmaggot in forum Programming
    Replies: 8
    Last Post: 06-30-2013, 07:18 AM
  4. Replies: 1
    Last Post: 05-04-2013, 12:19 PM
  5. Setting Criteria with a Lookup Field
    By winteram in forum Queries
    Replies: 1
    Last Post: 08-16-2011, 09:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums