Very very stuck!

[marklane2001]

An explanation into my problem:

I currently have a form (frm1) and within that form I have a list box (lst1)

The list box displays filtered pupil, course name, course cost, amount paid, amount remaing to be paid

When I click on the list box it opens another form (frm2) which allows me to enter financial payments against the course list box. e.g. course detail - Payment amount - This then runs and update query and this inserts a line into the tbl_All_payments - I then run another query (total) which groups all payments made against a pupil ID. after this has taken place itrefershes the list box an this updates the detail.

Another function I have on frm1 is to add a new course. This just alllows the user to press a button and frm3 opens. Within this form is drop downs fed from the class data. it then submits the detail into the same table as the ALL-Payments i.e. it inserts a line so the list box displays there is a new course but no amount has been paid yet

My problem is that when you add a new course it says the table tbl_All_payments is in use and cannot be locked. I can't work out why this is happening. Is this because I insert data to 1 table from 2 diefferent forms and queries?

 

[NigelShaw]

Hello Mate,

my guess is the table is open already with one of your other forms. to sucessfully enter data into a table, there are a few ways-
enter the data through a form that is bound to the table / query
leave the form unbound and retrieve / update data via code.

the latter is my preference as previous experience has given me issues as i dont like pop up forms so made a particular db with onl one form forcing all data to go through subforms. all ok except when entering new data. i had the same issues as you.

now i get my data through DAO and edit through DAO too.

regs,

Nigel

 

[marklane2001]

Thanks for your reply. So if I wanted to use DAO to close the connection to a list box how would I do that?

 

[marklane2001]

Hi . Thank for your reply. so how would I close the connection to a list box and reopen it again after I have updated the table using DAO

 

[NigelShaw]

Hi,

i would set the data to the form and link the listbox to that. unbound the form by removing its linked table or query which means the form has no data linked to it. then on a control like a button you could have-

Sub GetData()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = Currentdb
Set rs = db.OpenRecordset("YourTable/QueryName", dbOpenDynaset)

Me!Myfield1 = rs!Field1
Me!MyField2 = rs!Field2
' until you have filled your data

rs.close
db.close

Set rs = Nothing
Set db = Nothing

Me!ListboxName.Requery

End Sub

keep your listbox bound to its query that is supplying the data

then to update data, its nearly the same but the other way around in the sense of

retrieve data
form control = recordset collection

edit data
recordset collection =- form control

Sub EditData()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = Currentdb
Set rs = db.OpenRecordset("YourTable/QueryName", dbOpenDynaset)

rs!Field1 = Me!Myfield1
rs!Field2 = Me!MyField2

rs.close
db.close

Set rs = Nothing
Set db = Nothing

Me!ListboxName.Requery

End Sub

make sure the query that is underlying to the list box is changed before you update the listbox which is why i have put it last.

i'll do an example today for you.



Nigel