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

Topic

New Reply

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?

Reply | Quote

Replies

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?

Reply | Quote

Sorry about the confusion. I sometimes feel like I put TMI (Too Much Information) in my questions, and then when I don’t…

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. ***TMI WARNING—>> 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? Thanks.

Reply | Quote

Hi Randall,

Some remarks:

• 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.
• To refer to a column in a list box, use its Column property. Column(0) is the first column, Column(1) the second, etc.
• 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)

• 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

Reply | Quote

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 Currentdb.Execute method will not execute. When that procedure fires, the de###### pops up highlighting that line. CurrentDb points to the correct database. strSQL evaluates correctly. Do you have any suggestions as to what might be wrong here?

Reply | Quote

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 ) … “

Reply | Quote

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

Reply | Quote

OK Hans…

One more part of my form I can’t get to work right. I know, I know… I need to start paying you by the hour

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:

Private Sub lstCatResend_DblClick(Cancel As Integer)

Dim rstCatResend As Recordset
Dim var1 As String

‘set var1 to PopID
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

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 Set rstCatResend line, stating Type mismatch.

I know there is a limitation with OpenRecordset and linked tables, but tblVCXPlus_CatUpdt_temp is not linked. Any thoughts.

Thanks

Reply | Quote

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)

Reply | Quote

Thanks for your help. I bow to your superior knowledge

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

Reply | Quote