Update and export records.

[myhnews]

I would like to export selected records (from list box) to .xls file, also populate the same value to all selected records.
The value will be entered on the form once.

I'm attaching a small sample DB, so you can see what I have so far.

Any help is appreciated, the Export function, or the update record functions.

Thanks

 

[llkhoutx]

Use a query to select the rows (or value) you want to export and an update query to change a value or values in all rows.

 

[myhnews]

I have spent lot of time to figure this out. I would very appropriate if you can put the full code the I need to have. I'm not gigs on this. I already have code to export. I only need code to update field. Thanks

 

[JHB]

First, you need to use the correct recordset type, if you use the option dbAppendOnly, then you can't edit it!
Second, you can't edit an Autonumber field!
Third, you need find in the recordset the same InternalID as you have selected in the listbox.
So all in all it is easier to use an update query.

...  
For Each varItem In ctl.ItemsSelected
    db.Execute ("UPDATE tblOrder SET InvoiceNo = " & Me.txtOtherValue & " " _
    & "WHERE InternalID=" & ctl.ItemData(varItem))
  Next varItem
...

 

[myhnews]

This is the code I have. I tried many times, and it didn’t update.

I just realized that the tables are only linked to my front end. Is that the issue?

If yes, what line do I need to change?

The backend DB is at C:\ Databse\programBE

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblOrder", dbOpenDynaset, dbAppendOnly)


Set ctl = Me.List144
For Each varItem In ctl.ItemsSelected
db.Execute ("UPDATE tblOrder SET InvoiceNo = " & Me.txtOtherValue & " " _
& "WHERE InternalID=" & ctl.ItemData(varItem))
Next varItem

 

[JHB]

Try the attached database!

 

[myhnews]

This is what i have . i onlt pasted a part of it.

my issue is that it doesnt update the records. i get no error.


I realized that the tables are only linked to my front end. Is that the issue?

If yes, what line do I need to change?

The backend DB is at C:\ Databse\programBE

 

[JHB]

I've made a front and a backend, and it updates okay.
Try it, remember to relink the table.

 

[myhnews]

Wow. Very interesting experience. The issue was that the invoice number field was linked to an other table, Which didn't allow update. But I'm wondering why I didn't get error. Thanks for your help and sorry.