I also had hard time understanding the quotation marks
Any text or date/time type data needs to be delimited (quotes for text or octothorpes for dates) to be usable in sql or expressions. The difficulty often lies in concatenation, which is why it's always a good idea to assign sql (and sometimes expressions) to variables and/or Debug.Print them out for examination. Since a string must start and end with " as in "string here" any reference (e.g. field or variable name) that comes between the beginning and end must be delimited if it is
not a number. Using single quotes in the case of text makes this far easier. So if str1 and str2 are string variables then it must be evaluated as "...WHERE field1 = "pet" and field2 = "dog"" but the in-between quotes (and double at the end are incorrect)
Using single quotes, you try to have it interpreted as
"...WHERE field1 = 'pet' AND field2 = 'dog' " (extra spaces for clarity only - Access will remove them)
However, each part must begin and end in double quotes as I said, so you must write
"
...WHERE field1 = ' " & str1 & "
' AND field2 = ' " & str2 & "
' "
Looking at each portion, you have
...WHERE field1 = '
pet
' AND field2 = '
dog
'
Concatenate them and you get
...WHERE field1 = 'pet' AND field2 = 'dog'. You can prove that by copying/pasting the paragraph, go to end of each line in turn and delete the space at the end of each line. It should all fall into place.
Hope that helps with the delimiting part. Same approach for dates but as I said, using # rather than '
You can use double quotes entirely but it gets real messy and since I don't I'm not about to attempt to cover it here.
I had no problems referencing a value from another sub form using Forms!MainForm![SUB FORM]!ANumber.Value
I would go back and look at that again as it makes no sense to me. The 3rd parameter refers to the subform control. The only things I can foresee retrieving at that level would be properties of that control. To dig down to the subform itself requires invoking the .Form part AFAIK, then whatever you want to know about the subform itself. Most of what you'll find in research is examples where someone had a problem. I've cobbled together some info about the subject, which may help.
referencing CONTROLS on subform:
[Forms]![Main form name]![subform control name].[Form]![control name on subform]
Can also use what I call collections based syntax (if anyone knows the proper term, please let me know)
e.g. to get the record count of a subform:
Forms("MainFormName").Controls("subformControlName").Form.Recordset.Recordcount
NAVIGATION FORMS
[Forms]![NavigationForm]![NavigationSubform].[Form]![controlName]
(you cannot use a nav button for triggering code for this - it causes referenced form to unload)
Most nav forms are named NavigationForm by default and users don't bother to change the name or know they can.
[NavigationSubform] is akin to .Form (see 1st example above) thus should be used verbatim.