Cannot update Bound Textboxes 1

[cranebill]

I have a form and while any of the controls are Bound I cannot edit anything in them. If I unbind them, then I can type in the boxes. What would cause this? In the form properties I have allow edits set to yes and the controls arent locked.

Bill

 

[KenReay]

Hi

Is the underlying query updatable?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[cranebill]

How would I find that out? This query was made using the record source tab in the Form properties.

Bill

 

[KenReay]

Hi

Easiest way, run the query and try to change a column

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[cranebill]

Ok... apparently it is not updatable..

I did this just for kicks...

I changed the original Query...
SELECT [Customer Information Table].[Customer Name], tblInspSch.Phone, tblInspSch.Contact, tblInspSch.InspPONum, tblInspSch.InspPoExpires, tblInspSch.NoUnits, tblInspSch.LastInspDate, tblInspSch.NextInspDue, tblInspSch.InspSchDate, tblInspSch.Confirmed, tblInspSch.Note, tblInspSch.Comment, tblInspSch.Address, tblInspSch.City, tblInspSch.State, tblInspSch.Zipcode, tblInspType.Type, tblInspSch.Fax, tblInspSch.Ext, [Technician Table].[Technician Name] FROM (([Customer Information Table] INNER JOIN tblInspSch ON [Customer Information Table].[Customer ID] = tblInspSch.CompanyID) INNER JOIN tblInspType ON tblInspSch.InspSch = tblInspType.Id) LEFT JOIN [Technician Table] ON tblInspSch.Inspector = [Technician Table].TechID WHERE (((tblInspSch.NextInspDue)>=Date() And (tblInspSch.NextInspDue)<Date()+35) AND ((tblInspSch.Confirmed)=0)) ORDER BY tblInspSch.NextInspDue;

To this Query...
SELECT tblInspSch.Phone, tblInspSch.Contact, tblInspSch.InspPONum, tblInspSch.InspPoExpires, tblInspSch.NoUnits, tblInspSch.LastInspDate, tblInspSch.NextInspDue, tblInspSch.InspSchDate, tblInspSch.Confirmed, tblInspSch.Note, tblInspSch.Comment, tblInspSch.Address, tblInspSch.City, tblInspSch.State, tblInspSch.Zipcode, tblInspSch.Fax, tblInspSch.Ext FROM tblInspSch WHERE (((tblInspSch.NextInspDue)>=Date() And (tblInspSch.NextInspDue)<Date()+35) AND ((tblInspSch.Confirmed)=0)) ORDER BY tblInspSch.NextInspDue;

That enable me to update...
Unfortunatly I need more info than the second query is providing. How can I make the original SQL updatable?

Bill

 

[KenReay]

Hi

Hard to be absolutely sure by get rid of the Left Join would be my first step

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[cranebill]

I pulled out that join completely. I may have to get rid of all the joins it looks like and maybe have to Construct a basic query and have it do recordset type updates in the OnCurrent Event of the Form. Any other ideas before I rip it apart lol?

 

[jksmi]

Hi, try changing it to a SELECT DISTINCT or SELECT DISTINCTROWS... HTH, Jamie

 

[cranebill]

Ok I played around with it... made a simple query and then via vba had recordsets fill in the other fields. Now it take 10-15 seconds to switch between records. Does anyone have any ideas on how to get the initial SQL to be updatable?

Bill

 

[smuckers]

Good afternoon Cranebill:

Your original question was to see how the SQL string could be updateble. Review all the tables that are pulling data and look at the tables in the database relationships view. To be updateable, include those tables that are acting as bridges or connections between other tables.

Try this example as a starting point.

For instance, Table A is connected to table B which is connected to Table C. Table A is not directly connected to C.

Pulling a query just from A and C will exhibit characteristics similar to the problems you are experiencing. The data can be viewed, but can't update.

To make Table A or Table C updateble, include Table B in the query. Table B is the connection between Table A and Table C.

Include the connecting sets of fields between Table A and Table B in your query. Do the same and include the connecting sets of fields between Table B and Table C.

Your query is now updateable. So, to summarize, even though you do not directly use Table B, you must include all the connecting fields.

Your next question: 15 seconds?

Are you looking at one record at a time with many fields? If so, limit the dataset being pulled by the query.

Sometimes the creation of a temporary storage table will help. Pull just the data you need once and put it in the temp table. Your form query can then pull from the temp data, hopefully, at a faster speed.

Finally, Left Joins will most probably go through the entire list of data tables forming a cartesion dataset product table in memory. From there, the query finishes processing. So, 1,000 rows in 3 tables would give put 1,000,000,000(1,000 x 1,000 x 1,000) rows in a temporay table in memory while processing the query. Ouch!

Left Joins may be necessary, so limiting the data on the left side should significantly improve performance. One last note, remove one by one each of the columns. Continue removing columns. A significant improvement will be seen if your remove enough columns or a specific one. Many times only one column, usually one you do not need, is slowing everything down. If it is an important column, recode the query around it or put the information in a temp table and query specifically from that.

I hope some of these ideas help. Slow query speeds are frustrating and forms that do not update are more frustrating.

Good luck!

Smuckers


May your hysterical randomness be cured!