Yep, that's exactly what I'm trying to do! Indirect is right :)
Each Country only has one such table (a list of all the Provinces, States, or other for that country). The name of that table is stored in the ProvinceOrState Table field in tblCountry.
A little complex, but I couldn't think of any other way to keep these things well organized. Each "province" table also stores tax information for that province....
olivero
Last Activity: Mar 25 '08, 03:08 AM
Joined: Dec 31 '07
Location:
-
Hi Lysander,
ProvinceAbbr is a field in a table called tblProvinces_CA . This table name is stored within a field called ProvinceOrState Table that lives in a table called tblCountry.
I've hardcoded the WHERE statements with 1 and 8 to simplify locating the correct records during troubleshooting .
This is what it would look like without the sub query (this works as a query):
...Code:tmpProvinceAbbr:
Leave a comment:
-
subquery syntax question - sub query after FROM?
Hi again,
I'm trying to create a calculated field in a query that uses a sub query in the expression.
This is what I have:
ProvinceOrState Table is actually a field in another table.Code:tmpProvinceAbbr: (SELECT ProvinceAbbr FROM (SELECT ProvinceOrStateTable FROM tblCountry WHERE CountryID = 1) WHERE ProvinceID = 8;)
The sub query works on it's own and produces the value of the... -
So that worked!
This is what I used on the event of the button:
qrySetInvoicedT rueByOrderID is a simple Update query with the Order ID field and the Invoiced field. The Order ID field uses the DLookup aggregate domain function...Code:DoCmd.SetWarnings False DoCmd.OpenQuery ("qrySetInvoicedTrueByOrderID") DoCmd.SetWarnings True Forms!Main!frmSubInvoices2.Form.RefreshLeave a comment:
-
PDB,
Sorry for the delay. again. I put in some self study time and during this (while reading up on a completely different topic), I got the bright idea of just creating an Update Query object and calling that from the button on Sub1... In it, I could use the same DLookup for the criteria, and because the form is open, this should work.
I created the Parameter Update Query and tested it manually, and it works. I just...Leave a comment:
-
Another busy day... i haven't had a chance to work on this today, and I won't tomorrow. I'll need to follow up with you on the weekend again.
Thanks for the help so far, I appreciate it!
Oliver...Leave a comment:
-
the reference is correct. [Order Details] is a table. The control on frmSubInvoices1 is a combo box that uses a query to present the user with a list of orders that have not been invoiced yet - this query include the Order ID. The DLookUp grabs the OrderID from the record that the user chose and uses it as the criteria for the qryOrderDetails ByOrderID used in strSQL. It then pulls up all OrderDetails that are associated with the Order ID and presents...Leave a comment:
-
This mght help:
The error apparently happens if you WHERE points to a control on a form. This isn't what we are doing (though being able to do it would solve the issue).
Apparently, this error can also happen if the query requires a parameter. It doesn, because I'm using this as he criteria on the OrderID field in the query:
DLookUp("[OrderID]","[Order Details]","[OrderID] = "...Leave a comment:
-
ok, that line worked. It seems so simple when you do it...!
I still don't fully understand the purpose of each punctuation mark. Although I get it right about 80% of the time, it's more from guessing...
Now, I'm still getting the other error I had mentioned earlier with the following line:
CurrentDb.Execu te strSQL, dbFailOnError
Error = Too few parametres. Expected 1
I'll...Leave a comment:
-
PDB,
There's a punctuation mark missing in your example, so I've tried these syntices (and a few other too, I think), but I'm getting errors (Object doesn't support this property or method):
Forms!Main!frmS ubInvoices2!chk Invoiced!Form.R efresh
Forms!Main!frmS ubInvoices2!chk Invoiced.Form.R efresh
Forms!Main!frmS ubInvoices2.chk Invoiced!Form.R efresh
Forms!Main!frmS ubInvoices2.chk Invoiced.Form!R efresh...Leave a comment:
-
Hey there,
Sorry for the delay getting back. I've tried the code, though I had to adapt it slightly because the button is on Sub1 of Main and the Continuous Form is on another Sub form of Main, at the same level as Sub1.
Main
-->Sub1
-->Sub2 = same level as Sub1
It seemd to work through the SQL statements, but I got errors on the next two lines.
I had to change one line...Leave a comment:
-
I've been swamped this week and haven't had a chance to get back to this project! Sorry for the delay in replying to you. I hope to get back on this over the weekend.
Oliver...Leave a comment:
-
Your first method is certainly better and more efficient!
I got back late tonight and haven't had a chance to try this out yet. I'll keep you posted.
Oliver...Leave a comment:
-
Hi Puppydogbuddy,
Actually in this case, Sub2 is at the same level as Sub1 (I'm working on a different form now). I'll give this a shot and send an update, but I won't likely get to this until tonight.
Is there a way to do this by interacting with the form, instead of the query? I like how clean your method is, but I'm just curious. If this works for me, it will be the exception in the DB so far, as all my other changes...Leave a comment:
-
How do I check all check box controls on a Continuous Form?
Hi group,
How do I check all check box controls on a Continuous Form?
I have a form (Main) with two SubForms (Sub1 and Sub2). Sub1 has a button that is supposed to check all the check boxes visible in the SourceObject of Sub2. The SourceObject of Sub2 is a Continous Form based on a query.
I need to be able to click the button and have all the check boxes become checked.
Right now, I can... -
Continuous Forms view is EXACTLY what I was looking for!
With a little tweaking and hiding of unwanted lines and borders, and after removing the ability to edit the fields, I'm perfectly happy!
Thanks again guys!
OliverLeave a comment:
-
Thanks for the replies, I'll check out Continuous Forms and see if it will do what i need.
Cheers,
OliverLeave a comment:
-
Dynamic creation of fields based on results of a query?
Hi group,
Is there an easy way to make a form create the same set of fields dynamically, once for each record returned by a query?
I have a query that's being called from a form and the results are displayed in a subform in datasheet view. This is nice, but I want a better (and more visually appealing) way to interact with the query results.
Instead of displaying the results in datasheet view, I'd like... -
All my research points to that limitation as well.
I think I'm going to have to loop through the controls on the form, compare to the current tabindex, find the name of the next tabindex, then insert that. it' should be too complicated to do... :)
Thanks again for all the help!
Oliver...Leave a comment:
-
After getting it to work, I played around with things a little more and it was happening specifically because the focus was being removed from the sub2 Order Details form. The focus was removed for different reasons at different times, which is why I couldn't pin it down, but that's why.
Thanks for all the help, I really appreciate it!
OliverLeave a comment:
No activity results to display
Leave a comment: