Need help with Sql criteria and a Many to Many relationship (1 Viewer)

I can't duplicate it either. Perhaps I failed to click on 'enable macros' when I first opened the database. Just a guess.

Now, though, I still see only one state displayed in the state column of frmAddress. If I click on a suburb that is in South Australia, then South Australia fills all of the state column. Those suburb records that are South Australian show up, while those from other states are blank. Click on one of the blank suburbs and suddenly it shows up. It turns out its in the Australian Capital Territory. ACT now fills the state column. All the other suburb entries that are in ACT also show up. All those that aren't have turned blank.

This isn't quite what I want to do, but is interesting and related. I think I know the exact problem I am facing. I am going to do a new post on it. It should be a new post as this thread has run its course. Thank you for your help. I have solved the problem for which I re-opened this thread, and that was to do with lack of knowledge about the afterupdate event and the requery code. For further issues I had better create a new thread. I can't do that this evening unfortunately.

If you have converted the form to a Data sheet view, this DB will not work properly. It was designed to work in the single form view mode only, as soon as you start trying to use it in continuous or data-sheet view it will cease to work as designed.

Further cascading combo boxes do not work properly on continuous or data sheet views, as the cascaded combo will show the data related to the master combo in the record that currently hold focus.

David8 said:

I cannot view my form in Datasheet view (it will not show any data if I do). I really would like to.

Click to expand...

I know it was a few posts back, but I did say.

You keep talking about columns, there are not columns in the form

..........You're not looking directly at the tables are you ??

John Big Booty said:

..........You're not looking directly at the tables are you ??

Click to expand...

No. When I was talking about columns I meant form fields viewed in datasheet view.

I have a particular interest in form datasheet view, partly because when I started out with Access I didn't know what I was doing and DID use tables directly and got used to it, but partly because viewing one record at a time is very slow and cumbersome for how I want to use my database. I want to be able to see whole reams of records at a glance, and datasheet view does that very nicely.

...of course having a form in datasheet view does not work well with unbound form fields unfortunately.

David8 said:

...of course having a form in datasheet view does not work well with unbound form fields unfortunately.

Click to expand...

There is your problem, you are using my DB in a manner that it was never intended to be used.

Additionally cascading combo boxes do not work well in continuous or data sheet view as ALL will change to reflect the master combo in the field that currently hold focus.

If you want to have this sort of set up you need to use a query to populate your form and then have a pop up form to add items to your data set.

John Big Booty said:

There is your problem, you are using my DB in a manner that it was never intended to be used.

Click to expand...

That's a bit rich! I indicated that I want to use a datasheet view form in posts 14 and 15 of this thread, and you then provided your example in post 18 to see if it helped me to create a form that did what I was after (thank you for providing the example). I was only looking at the example to see how it could help after you'd suggested it to meet the requirements I'd outlined.

John Big Booty said:

Additionally cascading combo boxes do not work well in continuous or data sheet view as ALL will change to reflect the master combo in the field that currently hold focus.

Click to expand...

What about if you break with good database design and bind the master combo to a redundant field recording the master combo data on a line by line basis? Good database design is important to me, but so is having forms that behave how I want. After all they are the user end of database so they aren't unimportant either.

John Big Booty said:

If you want to have this sort of set up you need to use a query to populate your form and then have a pop up form to add items to your data set.

Click to expand...

I'll have to think this over. Initially it sounds very attractive, thank you. I have mulled doing something a bit like this. My idea had been to actually do a split view form. It would have been quite like your idea (perhaps?) as the top part of the split view would do the job of your popup (I guess anyway).
But I have no idea how to take my split view idea forward (if its even possible), nor your pop up idea forward. I shall do some googling on pop up forms when I get some time.

PS I know split view exists, just not sure if the top part can function differently from the bottom part.

David8 said:

That's a bit rich! I indicated that I want to use a datasheet view form in posts 14 and 15 of this thread, and you then provided your example in post 18 to see if it helped me to create a form that did what I was after (thank you for providing the example). I was only looking at the example to see how it could help after you'd suggested it to meet the requirements I'd outlined.

........

Click to expand...

....And I said;

John Big Booty said:

Perhaps the sample here will give you some ideas.

Click to expand...

Note that I indicated that perhaps it will give you some ideas, not that it would solve you problem.

David8 said:

..........

What about if you break with good database design and bind the master combo to a redundant field recording the master combo data on a line by line basis? Good database design is important to me, but so is having forms that behave how I want. After all they are the user end of database so they aren't unimportant either.

..........

Click to expand...

Any time you have calculated or dependant fields in a continuous or data sheet form you are going to get this behaviour, were all dependant fields reflect the result of the master field in the record that currently holds focus. The only. A cascading combo box set is no different, even if the master combo is bound all cascaded combos will always reflect the data held in master in the record that currently holds focus. This may mean that some show correctly whilst others are simply blank.

David8 said:

I'll have to think this over. Initially it sounds very attractive, thank you. I have mulled doing something a bit like this. My idea had been to actually do a split view form. It would have been quite like your idea (perhaps?) as the top part of the split view would do the job of your popup (I guess anyway).
But I have no idea how to take my split view idea forward (if its even possible), nor your pop up idea forward. I shall do some googling on pop up forms when I get some time.

PS I know split view exists, just not sure if the top part can function differently from the bottom part.

Click to expand...

I'm not sure that a split form is going to work either as it is simply a special case of a data sheet form, if I understand them correctly.

John Big Booty said:

....And I said;

Note that I indicated that perhaps it will give you some ideas, not that it would solve you problem.

Click to expand...

Yes, and you'll notice I didn't raise any objection to your post #18, where you said perhaps.

It was you saying:

"you are using my database in a manner it was never intended to be used"

that I was reacting to. I wasn't using it in any such manner, only investigating it to see if would help me, as I think you originally intended me to investigate it.

It would seem we were both working at cross purposes, for which I apologise.

A bit of a cludge, but here is one way to make it work on a continious form: http://www.blueclaw-db.com/download/continuous_dependent_combo_box.htm

JR

JANR, thank you for that. I believe it is an attempt to tackle the problem I'm facing. However, I have to admit I don't like it.

I want a clean 'active spreadsheet' kind of look that datasheet view gives me. With row after row of records with many fields, all those drop down arrows in the example you linked to will look a bit of a mess. Quite apart from that, I've downloaded their example and the "cludge" doesn't even work properly. For example, I tried selecting Billy Bob as the first advisor, with student Hugo Chavez. For the second advisor I chose Molly Dean and George Bush. George Bush then gets laid over the top where it should say Hugo Chavez, and stays that way going down the form. Some entries for the student field ended up looking blank even though they weren't. But even if it did work I don't really like it.

Is someone going to come out and tell me that Datasheet view forms are crippled? Everyone tells me not to use tables for data entry and review, you must use forms, and oh, how about you try datasheet view if you want it to look like that. And then I find datasheet view being extremely restrictive. I cannot understand why people are willing to accept these one-at-a-time or few record forms? For many purposes they are just not adequate.

And you cannot just say 'OK, build a report' because for some audit style purpose you want a form that looks like a report, but is interactive. A report that shows you what you should be looking at, and allows you to quickly edit to match what you are actually looking at. To edit as you check the data, in bulk.

Buildings, with rooms full of IT kit are a case in point. If I can see there are 14 monitors in a room all of the same make and model, I don't want to be going through my form ONE RECORD AT A TIME when I'm doing a quick audit. That would be horrendously slow. Datasheet view on a laptop is great, it allows me to quickly count that there are 14 of a certain type of record corresponding to that room, while I stand in that room and see. If another record is wrong, I can quickly change it - impossible with a report on a sheet of paper. But cascade lookup data entry in datasheet view seems crippled.

There must be a way to code my way out of this, no? I want to get datasheet view behaving itself.

http://www.access-programmers.co.uk/forums/showthread.php?t=201285