Query Supplied Listbox Names And Textbox Dates

[lhooker]

I have a form (with a listbox) that gets it's listbox names from a query. I can retrieve names from listbox (via ---- '=[List7].[Column](0)') I also have a textbox on the same form. The textbox is not providing the date from the same query results. What is the problem ?

 

[isladogs]

I'm slightly confused by the question.
If what follows is completely irrelevant, just ignore it.

Queries cannot handle column references from listboxes
Either set a variable equal to that value or set a (hidden) textbox equal to it.... then use whichever one you choose in your query

Having said that Column(0) is the default so wouldn't normally need to be referenced ...unless you have set a different column as the bound column

 

[pbaldy]

Why would it? You haven't shown any code or formula. At a guess, make sure the column count property of the listbox I ez correct.

 

[lhooker]

Ridders/Pbaldy,

Attached is a sample of what I was referring to When a name is clicked on in the listbox, the 'Last Paid' date should be insert into the textbox, then a new record is inserted into the 'Bills' table.

 

[isladogs]

Whenever you use code or a query to run an action query, it will only work if the table has a primary key field.
In fact, as a general rule, add a PK field to all tables to prevent issues.

In your case, add an autonumber field BillID as your primary key.
The button code will then work but your form isn't as clearly designed as it could be having two unmarked textboxes at the top.

Other issues:
1. Add a Boolean Paid field to your table and bind the checkbox to that.
2. Use date delimiters for date field #" & mydate & "#
3. As already stated, column(0) is the default so can be omitted
4. Entering a name in the Bill_Name textbox adds it to the list - that's also why you have blank rows in the list
5. Your button code could be 'simplified' e.g. to

Private Sub AddRecord_Click()
          
On Error GoTo Err_AddRecord_Click

CurrentDb.Execute "INSERT INTO Bills ( Bill_Name, NextPaymentDate )" & _
    " SELECT '" & [Forms]![Bills].[List7] & "' AS Bill_Name," & _
    " #" & [Forms]![Bills].[NextPaymentDate] & "# AS NextPaymentDate;", dbFailOnError

Exit_AddRecord_Click:
    Exit Sub

Err_AddRecord_Click:
    MsgBox Err.Description
    Resume Exit_AddRecord_Click
    
End Sub

6. Your append code doesn't include the PaidOn or Category fields - I'll leave you to add these

 

[lhooker]

Ridders,

Thanks ! ! ! I'll add these fields.