Query Results appear read-only

[tt1611]

Good Afternoon all
I have 2 forms that are both based (bound) by a query (Query A). My query has 9 tables in tables each joined to one central table by left Joins.

When the query is run, i need to be able to make changes to or update the records on the fly.

To my strange surprise, I am now noticing that the results are all read only. This really defeats the purpose of my design and I am leaning towards a make-table query as I really need to be able to make changes to these results. Can anyone give any feedback on this?

Thank you for your help in advance.

 

[Trevor G]

If it is a complex query it will make the records read only when you view the result.

 

[tt1611]

Not sure what you mean by "complex" here Trevor. I guess my question is, is there anyway to get around this?

 

[Trevor G]

How many tables are being used?

If you want to edit them can you create a form to the tables with subforms?

 

[boblarson]

A common mistake people make is trying to include too many tables in their forms. You also need to make sure that your database is properly normalized. Once that is done you use forms/subforms (as TrevorG has stated) which allow you to have the tables with one-to-many or many-to-many relationships to work together. The main form has the side with the ONE in it and then the subform has the related table which has the MANY to the main form's one record.

 

[tt1611]

OK
Thanks Bob and Trevor for your suggestions. I probably need to upload a sample of what my form looks like right now so you guys can understand my dilemma..

At my office we are trying to track all our IT assets - PCs, local printers, cash draws, scanners etc.

I have the PC as the parent table (with PC info) and I have all other peripherals as above that are connected to a PC as the child tables - this naturally is a one-many relationship.

Each child table has the PC serial as an FK. My problem is we want an update system (which is what my form has right now) where I can call the serial number and model for all the child devices associated with a particular PC (inner join) and then we want to be able to fill in these blanks - and thats where my problem is...the join works fine i have the PC, name, model, serial etc joined to the printer name and model, the scanner name and model, the cash regsiter name and model etc ( each PC can have up to 8 devices plugged in although not all do hence the reason for my numerous tables)

So like i said my query works fine giving me all the info in the multiple joins but I am unable to update this sheet to include the relevant missing information we have on 5000 xls sheets.

In my form, I have a search box and a subform at the bottom. The idea is when we enter a PC name (serial), it populates the subform with all devices (missing or present) below and we can update on the fly. Are you suggesting up to 8 sub forms on this main form? Didnt think this would look good in design

 

[vbaInet]

Maybe this will throw some more light:

http://allenbrowne.com/ser-61.html

 

[tt1611]

Thank VBA i had actually come across that post yesterday...What I am hoping to get help with if at all possible is a suggestion on designing this query where I CAN make edits to the tables on the fly because I dont know where else to go. Might have to end up throwing all 30 -40 fields into one table.

 

[vbaInet]

Which one in the list of things mentioned applies to your query design?

 

[tt1611]

"It uses JOINs of different directions on multiple tables in the FROM clause. Remove some tables."

My joins are all left joins and I am unable to remove some of the tables if i need the query to retrieve the information i need from specific tables.

I am attaching my query as it comes from access along with a snapshot of my relationship so you can get a clearer picture of what I am trying to do.

Just so you know, my parent table is actually the PC (the actual PK and FK is the Serial_Number) but my boss wanted some changes in the system hence the reason we are using device_name.

Please ignore the SQL parent table (not sure why access always does that)

Thanks

 

[vbaInet]

You have too many joins which is where the word "complex" comes in.

You would need to use subforms in place of the joins for each of your tables. The subforms will link to the main form whose record source will be PCs. You can use a tab control to breakdown your form into a much more logical layout.

 

[tt1611]

Thanks for all your help VBA. I guess the final thought of the day is that I am never going to be able to have a row/record where ALL related information pertaining to the PC is present.

So in my main form, I will have the user select a PC and then run code (PL/SQL) that populates each sub form (in seperate tabs) with the relevant PC peripheral (as you have suggested).

That should hopefully have read/write capability seeing..(just thinkin out loud). Ok i'll give that a shot. Do you have any other thoughts on this? (Again can I say thank you to you all for all your help with this).

 

[vbaInet]

You can implement a kind of split view with all the records viewable on one line and below that have all the relevant subforms that will allow the user edit the currently selected record.

With a subform you don't need code for updating the records as long as you have it linked via the Link Master/Child Fields properties.

 

[tt1611]

OK
Took the idea to my super an he likes it..We will go with the tabbed view each showing a separate device linked to a combo box on the main page where the user selects a PC name.

I am having to run very simple code as he would want to search for a name of a device with the LIKE function

eg show me all devices (in each tab) where name LIKE "IBM11*****".

You have been a real great help...guess now i know access does not do too well with complex joins and honestly I probably wouldnt too if I was asked for all that information.

 

[vbaInet]

Here are some ideas for you:

1. Give your users two ways of searching, via a text box (which will be used for the LIKE search) and via the combo box (like you already have)
2. Make the first tab the search tab and display the related records on the other tabs
3. Each tab will have subforms linked to your main form

If you're going to allow users perform a LIKE search then you need to provide a way for them to select the record they want to view after the LIKE search is performed. A subform on the first tab (i.e. the search tab) comes to mind here.

 

[tt1611]

Brilliant Vba.

I think when they run the LIKE search..ill implement it where on double click of their chosen machine, the other subforms (tabs) will auto populate.

Cool

 

[vbaInet]

You've got it tt1611. I see you don't mind getting your hands dirty

Maybe just a single click will suffice since the user might be faced with many records and he/she wouldn't always want to double-click just to view the full details.

Good luck!! (And let us know how you get on.)