Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Programatically Adding record to recordset (AXP (2002) SP-1)

    It should be fairly strait forward to add a record to a recordset, but I'm having problems with this one. I have created a form with a text box and two listboxes. Here's how its suppose to work: Listbox1 has a query as its rowsource. Listbox2 has a table as its rowsource. Type in a last name in the textbox, which in turn automitically searches listbox1 for a matching record. This part works fine. Double-clicking the searched for name in listbox1 is suppose to add that record (actually, just the PopID from that record) to the table represented in listbox2.

    I know I need to create a recordset and use the AddNew method. In order to get the search feature of listbox1 to work correctly, I set the bound column to a column other than the PopID column needed for the AddNew method to work. Now, when I use AddNew and reference listbox1, the bound column populates my table. I need to have another field from listbox1populate the table. How do I reference this other field?

    Thanks for all your help?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Programatically Adding record to recordset (AXP (2002) SP-1)

    I'm sorry, but I'm confused. What does AddNew have to do with a search feature in this listbox... or are they comboboxes (not the same thing in Access) .. if you're searching on listbox1 and adding a record to the table that populates listbox2? If all you're trying to do is insert the PopID into another table, why not use that as the bound column in listbox1?
    Charlotte

  3. #3
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Programatically Adding record to recordset (AXP (2002) SP-1)

    Sorry about the confusion. I sometimes feel like I put TMI (Too Much Information) in my questions, and then when I don't... <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    I use the textbox' Change Event to trigger a Function that uses the FindFirst Method on the list in Listbox1. This sort of works like a combobox search in that with each letter that is typed into the textbox, the listbox scrolls down to the appropriate spot in the list. The combobox search doesn't keep the list open, while this type of seach does.

    The recordset (a query, actually) for the listbox has multiple columns. In order to make the listbox search solution work, the field I'm searching on must be the bound field. For example, since I'm typing in the last name in the textbox, I have bound the listbox to the column containing the last name. (Actually, a concatenated LastName-comma-FirstName field).

    The purpose of searching through the listbox for a particular name is that I want to double click on the item in the list and have a value from a different column populate to a table. <font color=blue>***TMI WARNING--->> </font color=blue> This particular form is to be used in management of a membership group. Throughout the week, I will be "selecting" member records that require a specific action. I want to add the PopID to a table. At the end of the week, I process the records in the table using another procedure, clear the table and start over the next week.

    So, I want to double-click on a record in the listbox and have a value from the record added to a table. AddNew first comes to mind, but since I want to add a value other than the bound column, I'm unsure how to proceed. Any hints? <img src=/S/help.gif border=0 alt=help width=23 height=15> Thanks.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Programatically Adding record to recordset (AXP (2002) SP-1)

    Hi Randall,

    Some remarks:
    <UL><LI>I don't think that the field you're searching on needs to be the bound column. I created a small test form with a text box and a list box having three columns; the first (hidden) column is the bound column, but the text box searches on the third column. It works fine.
    <LI>To refer to a column in a list box, use its Column property. Column(0) is the first column, Column(1) the second, etc.
    <LI>To insert a record in a table, you can use DAO or ADO, but it is probably easier to assemble the SQL string for an append query in code and execute it:

    Dim strSQL As String
    strSQL = "INSERT INTO tblNeedAction ( PopID ) Values (" & lbxMembers.Column(0) & ")"
    CurrentDb.Execute strSQL

    (substitute the appropriate names)
    <LI>I would probably go about this completely differently. I would not use a separate table, but add a field to the members table to keep track of those needing special action. It could be a Yes/No field with default value No (Yes meaning "needs special action"), or a numeric field if you want to distinguish various statuses. I would create a continuous form that includes this dedicated field (check box for a Yes/No field, combo box for a numeric field that displays descriptions of the statuses). You could still use a search text box in the header or footer section.

    The advantage of this approach is that you can see at one glance whether a member has been singled out for special action, and you can filter on this status.

    To process the "selected" members, you can use a query that selects them. To clear the status field, you can use an update query that resets the status field to False or 0 or Null, whatever is appropriate.[/list]HTH

  5. #5
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Programatically Adding record to recordset (AXP (2002) SP-1)

    Hans--

    Thanks for your response. I like your idea of just adding a field to the members table to toggle whether that member needed attention. However, I am reporting out of a SQL database that I cannot modify. So everything I need to do has to be done without altering the underlying database. It presents some challenges sometimes. (Just to be complete, I have created the Access database by linking to all the SQL tables. Only then can I write queries, design forms and add supplementary tables.)

    Your suggestion about using an SQL statement really hit the mark. However, the <font color=blue>Currentdb.Execute</font color=blue> method will not execute. When that procedure fires, the de###### pops up highlighting that line. <font color=blue>CurrentDb</font color=blue> points to the correct database. <font color=blue>strSQL</font color=blue> evaluates correctly. Do you have any suggestions as to what might be wrong here?
    Attached Images Attached Images

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Programatically Adding record to recordset (AXP (2002) SP-1)

    Randall,

    In your screenshot, I notice INSERT INTO tblVCX+CatUpdt_temp ...

    The + in the table name throws the SQL parser off. Is tblVCX+CatUpdt_temp really the name of the table?

    If so, you should enclose the entire name in square brackets: INSERT INTO [tblVCX+CatUpdt_temp] (or better still, not use a + sign in table or field names)

    If not, I presume that tblVCX and/or CatUpdt are variables. If so, the variables should be outside the quotes:

    strSQL = "INSERT INTO " & tblVCX + CatUpdt_temp & " ( PopID ) ... "

  7. #7
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Programatically Adding record to recordset (AXP (2002) SP-1)

    Yes, tblVCX+CatUpdt_temp is a table. After enclosing it in brackets, it worked like a charm. Thanks for your help!!

  8. #8
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Programatically Adding record to recordset (AXP (2002) SP-1)

    OK Hans...

    One more part of my form I can't get to work right. <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23> I know, I know... I need to start paying you by the hour <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

    The piece you helped me with earlier today added a record to a table. This one is suppose to delete a record from the table (in case the wrong one is added, or something).

    Here's what I have:

    <font color=blue>Private Sub lstCatResend_DblClick(Cancel As Integer)

    Dim rstCatResend As Recordset
    Dim var1 As String

    <font color=448800>'set var1 to PopID</font color=448800>
    var1 = Me!lstCatResend.Column(0)

    Set rstCatResend = CurrentDb.OpenRecordset("tblVCXPlus_CatUpdt_temp", dbOpenTable)
    rstCatResend.Index = "PopID"
    With rstCatResend
    .Seek "=", var1
    .Delete
    End With
    rstCatResend.Close
    Me!lstCatResend.Requery

    End Sub</font color=blue>

    You'll notice I took your advise and changed the name of the table.

    This is the same code I used in another database. It worked there, but here I get a runtime error on the <font color=blue>Set rstCatResend</font color=blue> line, stating Type mismatch.

    I know there is a limitation with <font color=blue>OpenRecordset</font color=blue> and linked tables, but <font color=blue>tblVCXPlus_CatUpdt_temp</font color=blue> is not linked. Any thoughts.

    Thanks

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Programatically Adding record to recordset (AXP (2002) SP-1)

    OpenRecordset is a DAO method. You must set a reference in Tools | References... to the Microsoft DAO 3.6 Object Library in order to be able to use it.
    To avoid ambiguity in the declaration of rstCatResend, use

    Dim rstCatResend As DAO.Recordset

    (Recordset is also an ADO object)

  10. #10
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Programatically Adding record to recordset (AXP (2002) SP-1)

    Thanks for your help. I bow to your superior knowledge <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

    I did have the DAO 3.6 reference set, but the ADO 2.1 reference was set with a higher preference.

Posting Permissions

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