Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    shylock is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Dayton, Ohio
    Posts
    100

    Text box to List Box

    I am having trouble inserting the contents of a text box into a list box. I keep getting the Error: "Run-time error '424' Object required.". I have the foolowing code:


    Code:
    Private Sub Form_Load()
        Me.lstAssocPhysServ.Column(0, 1) = Forms!frmEditLogicalServers.txtPServ
    End Sub
    Am I putting this in the wrong spot or is there something else happening?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You don't add to the listbox, you add to the table connected to the listbox.
    run an append query,then update the listbox.

    docmd.openquery "qaAdd1Itm"
    me.lstbox.requery

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    ...as long at the listbox rows are not from a value list.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    shylock is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Dayton, Ohio
    Posts
    100
    hanks, ranman256, but the form that Me.lstAsscoPhysServ is on contains 2 list boxes. The first list box contains a list of Physical Servers from the Physical Server table and the second list box should or will contain Physical server(s) chosen e first list box. The first list box (Me.lstPhysServers) already contains the physical server referred to in Forms!frmEditLogicalServers.txtPServ. I want it to appear in Me.lstAssocPhysServ when the Me form opens or gets loaded. At that pint the user can select the physical server from Me.lstAssocPhysServ and then press a button to Remove it and then choose a physical server from lstPhysServers and double click it or press a button to add it to lstAssocPhysServ.

    It seems that it would be useless to "add to the table connected to the listbox" since it is already in the table.

    Micron: What do you mean by a "value list? I assume you mean a list that the user types into the list?

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Excluding already selected servers would have to be a query that joins with the junction table - basically a 'find unmatched' query.

    Actually, maybe this should be the RowSource for lstAssocPhysServers to select a server for a new record.

    Be aware that combo and list boxes using alias that conditionally display items in RowSource don't work nicely on Continuous or Datasheet view form.

    Combo and list boxes can have RowSource based on a table/query or a text list typed into (or added by code) in the RowSource. RowSourceType has to be set to either Table/Query or ValueList as appropriate.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A semicolon seperated list that developer enters into row source property in property sheet.

  7. #7
    shylock is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Dayton, Ohio
    Posts
    100
    Thanks one and all. You have confirmed what I thought. I will use a temporary table that has an added column for "isSelected" set to false and then set the IsSelected field to True for the Server Name that is selected. I will set the rowsource to query the temp table.

    One final question, how do I delete the temporary table?

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Don't. Just delete records before the process runs.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    shylock is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Dayton, Ohio
    Posts
    100
    June7:

    I have already been creating temporary tables, but each time I start up the project, I get a pop up message saying that the temporary table(s) already exist. Is there a way to suppress this message? If there is, I don't have a problem in not deleting the temp. table(s).

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You have code that executes automatically upon db open. Don't use MAKE TABLE actions. Create the table once (manually probably just as fast as with code) then use INSERT and DELETE actions each time process is run.

    Repeatedly creating and deleting tables is modifying db design and causes db bloat.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    shylock is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Dayton, Ohio
    Posts
    100
    June7:

    Thanks very much for your answer. I'll do as you suggest. Don't know why I didn't think of this. I guess it must be because I'm out of practice. As I said once, a few months ago, I haven't done any programming for about 10 years and no Access since the first version.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    A temp table may be a way to resolve but I am not really understanding why you need it. Query I described should be able to produce the desired list.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by June7 View Post
    You have code that executes automatically upon db open. Don't use MAKE TABLE actions. Create the table once (manually probably just as fast as with code) then use INSERT and DELETE actions each time process is run.

    Repeatedly creating and deleting tables is modifying db design and causes db bloat.
    Although that is the standard advice an I have stated the same myself on many occasions, using APPEND queries creates the same file size increase as MAKE TABLE. Also MAKE TABLE queries are generally faster. See this utility created by the DBGuy http://thedbguy.blogspot.com/2016/02...end-query.html
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #14
    shylock is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Dayton, Ohio
    Posts
    100
    Let me eplain why I need the temporary tables. For example, in order to choose which services are contained on a server, I have a form with 2 list boxes side by side. The first list box contains all available services. I want to select services from this list box and put them in the second list box. The second list box is then saved to the server. From time to time the services on a server may need to be removed or a new service added. I create a temporary table, for ALL the Services (The first list box) with a NEW field named IsSelected. This field is initially set to false . When a service is selected the IsSelected field is set to True. (The original table does not contain this field.) The temporary table must be dynamic as the services in the original table may change.

    I hope this explains my purpose a little better.


    The question remains from my post #9: I get a pop up message saying that the temporary table(s) already exist. Is there a way to suppress this message?

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by isladogs View Post
    Although that is the standard advice an I have stated the same myself on many occasions, using APPEND queries creates the same file size increase as MAKE TABLE. Also MAKE TABLE queries are generally faster. See this utility created by the DBGuy http://thedbguy.blogspot.com/2016/02...end-query.html
    Don't mean to come across as condescending, but I don't agree with the notion there that there's only 2 ways to create a temp table as per the link. Same as a querydef, if you don't give it a name, it only resides in memory and that would make it at least 3 ways. Thus something like this
    Code:
    Set tdf = CurrentDb.CreateTableDef("")
        With tdf
            'AutoNumber; Long with the attribute set.
            Set fld = .CreateField("ID", dbLong)
            fld.Attributes = dbAutoIncrField + dbFixedField
            .Fields.Append fld
            .Fields.Append .CreateField("f1", dbLong)
        End With
    adds no bloat at all. It reports the same original db file size every time.

    Not being as familiar with tabledefs as I am with querydefs, I don't know if there's another way to create one. I would have thought that using an existing table, or perhaps sql statement, that it could be done. As far as the sql method is concerned, it would seem to require the name of a table to insert into, and since a tdf object that resides only in memory has no name, I don't see a way to use the method.

    I would have preferred if the sample db left the original file size in the textbox rather than recalculating each time. As for the calculation, I have read that you're supposed to use the LOF function on open files and not GetFileSize(). I also find that no matter which of the 3 methods I use in the sample db, the time it takes is about the same, and it's always under reported by about 10 seconds or so; sometimes it's just 0 when it's really about 10. I think the timer part is adding time to an execution that actually takes less than what's reported.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. List Box to Text Box
    By manics31 in forum Forms
    Replies: 3
    Last Post: 11-01-2018, 11:11 AM
  2. Replies: 2
    Last Post: 03-07-2018, 11:00 PM
  3. Replies: 8
    Last Post: 12-13-2017, 10:38 AM
  4. Use List box to populate text box with existing text
    By Stephenson in forum Programming
    Replies: 5
    Last Post: 10-07-2016, 10:07 PM
  5. list and text box??
    By Zugalug in forum Forms
    Replies: 2
    Last Post: 10-04-2011, 10:53 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