Query on a subform

[mig144]

Dear List,

I've made a subform which is based on a query. One of the fields from this "Many" table is "Product Type", and the query asks to list all the product types except "Delivery" (in other words, 'not "Delivery"'). I don't want to see the Delivery items in this list.

The trouble is, the subform no longer has the blank row with the asterisk which allows you to add a new record into it.

I really need this subform to take new records.

What should I do?

Mrs Miggins.

 

[RuralGuy]

On the data tab of the properties sheet for the SubForm set "Allow Additions" to Yes

 

[mig144]

It is already set to Yes.

 

[RuralGuy]

OK then, you probably now have a non-updateable query bound to the subform. See if you can make a change to anything. If it is not updateable then use Access help to search for troubleshooting queries and look under the Select query. This is one of the times when the help will take you to where you want to go.

 

[mig144]

Thank you very much for your reply.

The data cannot be updated. I looked at the help. I didn't understand it.

I am really struggling to find an alternative way to deal with the data.

Maybe I should try the queries section.

 

[RuralGuy]

There are a number of reasons for queries to not be updateable. Since you said you want the "blank row" back, I will assume it was updateable at one time. You might want to backtrack with a copy of the query until it gets updateable again. Then come foreward until it quits updating and you will know what is causing the problem. You can test the query right in the Query Builder and see if you can change anything.

 

[mig144]

Thanks.

The blank row existed before I made the subform from a query. Before that it was just a subform, not based on a query.

 

[RuralGuy]

If it wasn't based on a query/table then what did you display in the SubForm?

 

[mig144]

It was based on a table, not a query

 

[RuralGuy]

Can you make a query of just that table and use it. It should be updateable. Then start adding whatever you have in the current query 'til it stops updating.

 

[mig144]

It won't let me. When I try to add it to the main form as a sub form it whines that the link I selected cannot be used, and I should go back and add the missing fields.

So I've gone back, used all the necessary tables in the query, and the same thing is happening. It won't allow changes or new data to the subform.

 

[RuralGuy]

OK, I think we're missing something here. If your subform worked as a subform when it was bound to a table then it *will* work if you create a query for that table and bind it to your subform. The Form or SubForm can not tell the difference between a query and a table.

 

[mig144]

I've tried to do that and it doesn't work. It needs a link. I don't know how to use all the right terminology to describe why it doesn't work and I have spent 3 days on trying to get it to work. The subform has to be specific to the record shown, so just making a query of the table which is the source for the subform....how do I *bind* it?

This is really upsetting because it must be so simple to filter out the product type "Delivery" from the inventory, but I have a gap in my brain which prevents me from understanding how to do it.

I've made a query from the Products table (the source for the subform) called "qryProductsNotDelivery"

I go to my form "frmAddress, I go to design view and click the subform icon and draw a box where I want my subform.

The wizard kicks in. I choose "use existing tables and queries", "next", and choose "qryProductsNotDelivery", select all the fields and click "next".

I choose "Show qryProductsNotDelivery for each record in tblAddress" and click "next".

The subform which the wizard produces does not have updateable records.

 

[RuralGuy]

I can be very frustrating at times. It sounds like you are doing everything correctly so I'm not sure why it is not working. Would you care to post a copy of the db with anything sensitive removed but enough data to demonstrate the problem. Maybe someone here could look at it and determine the problem.

...how do I *bind* it?

Go to the data tab of the properties sheet of the SubForm and use the dropdown arrow to select your query as the Record Source. That's it!

 

[mig144]

Sadly I do not have a zip programme. I am at work and we are not authorised to download programmes. I know I should have one as part of WindowsXP but it is missing. I'd love to send you my db with all records deleted, but it cannot be.

 

[RuralGuy]

Then we have to proceed as we are. If you *bind* the table do you get updateability back? If you do a vanilla query of the table with all fields and *bind* it do you get updateability?

 

[mig144]

Dunnit!

Thanks for all your help. I don't know what changed, but it now works. Instead of making a subform, I made a subform sized gap and linked the simple query of the products table (including the field from the AddressID) to it. I can't really see the difference in what I have done, but it works.

Thanks so much. Thank god it's over! Now for the other embellishments....

Mrs Miggins

 

[RuralGuy]

Outstanding! Thanks for posting back with your success.