Access itself does not distinguish between methods of creation of tables, so there is no in-built way to tell if it was as a result of a make-table query as opposed to manual for instance.
-Stewart
Stewart Ross
Last Activity: Sep 19 '24, 10:37 AM
Joined: Feb 12 '08
Location: Beauly, near Inverness, Scotland
-
Access will only lock a table if something bound to that table - a query for instance - is open, or another object such as a form based on that table remains open.
Without seeing the code involved it is difficult to offer an opinion, other than to say the debugger will be telling you the truth - there is something bound to that table which is still open at the time you are attempting to run the make-table query. Are you sure you have...Last edited by Stewart Ross; Feb 8 '17, 04:33 PM.Leave a comment:
-
The values you are trying to add and subtract are being converted to text by the database engine's interpretation of the result returned by the non-null function (Nz) - which is why 75 + 295 is returning 75295. In your screenshots you will see that these values are left-aligned - a give-away that they have been converted to text. Numeric values always default to being shown right-aligned.
If you want to continue using Nz you should wrap...Leave a comment:
-
You have mistakenly defined a sub within a sub, hence your error message. Remove line 3 in the code above (Sub Msgbox_Yes_No() ) - it is not required and is the source of the specific error you mention. Not that you needed to, but please note that you cannot nest a sub or function definition within another sub or function - it is not valid syntax.
You will also need to add an End If to complete your first IF statement (line 2), otherwise...Last edited by Stewart Ross; Apr 1 '16, 12:54 PM.Leave a comment:
-
You are assuming that the conditions you have joined with 'and' apply to the field 'position' in the first part of the statement
position = "driver" and "labour" and "civil engineer" and ...
but that is not so. Each test must explicitly state what field or expression you are testing. Also, I am sure you are wrong in using 'and' in your tests, as the IIF statement would never evaluate to True...Last edited by Stewart Ross; Feb 25 '16, 07:39 PM.Leave a comment:
-
Stewart Ross replied to MS Access: Query rejects sorts when calculations are made field with null valuein AccessOne way to ensure that the [Age] field aliasing the function call always has a numeric value is to wrap the function call inside an Nz statement which returns a specified value if the inner statement is null. As Nz would return a value treated as text to the query this in turn has to be wrapped in another function such as Val or CLng to convert the text value to a whole-number:
In the statement...Code:Age: CLng(Nz(fn_Age([DOB], 0)))
Last edited by Stewart Ross; Jan 30 '16, 06:11 PM.Leave a comment:
-
You are on the right track in thinking about your recordsource as your main query. You can either build the WHERE clause in VBA, or you can build a filter string and apply that to your form as a filter instead. Either way, you need to use correct syntax for your WHERE clause (and a filter string is just a WHERE clause without the WHERE keyword). That means you if you want to use the keyword AND to join sections of your clause together you must work...Last edited by Stewart Ross; Oct 5 '15, 05:42 PM.Leave a comment:
-
You've said that the text searches work, but I'm not so sure that this is so. You are using separate IF statements to test your combos. Assume for the moment that just the first combo, cboSearchLastNa me, has a value, so the IF at line 3 is satisfied and none of the other IF statements is executed. By the end of the first set of SQL build statements at line 14 you have a dangling AND at the end of the Where clause:
...Code:" WHERE LastName
Leave a comment:
-
There is no third-party application I know of which can do this. Normally, Compact and Repair is done using Access itself.
By the way, Compact on Close (which you mention your Delphi application does) is not a recommended approach - it can itself cause application errors in some cases.
-StewartLeave a comment:
-
On the basis that there is nothing obvious wrong in what you have posted I suspect that this sub may not be executed as it stands. If the sub folder Egypt was incorrectly named, say, Excel would tell you that the path was invalid. If you have Excel's warning messages turned off then you would not get notification of an error, but nor would the file be saved. Saving one folder up is not behaviour that I recognise.
I suggest you set a...Last edited by Stewart Ross; Aug 19 '15, 05:24 AM.Leave a comment:
-
Hi Seth. You are right that you can test the form's Filter property to see whether a Where clause exists, but to know for sure whether the given filter is active you need to test the form's FilterOn property which will be set True if the filter is currently applied, False if not. It is possible to have a form's filter property set to some value but for the filter not to be applied at the time.
-StewartLeave a comment:
-
Hi Ajay. You will need to ask the universities concerned directly - we cannot give a general answer on their behalf. In Britain candidates would normally need to have an honours degree - a four-year degree - before entry to a Masters programme, but this varies from country to country and sometimes from institution to institution. You really will have to find this out for yourself from the admissions departments of the universities concerned. ...Last edited by Stewart Ross; Jul 3 '15, 11:42 AM.Leave a comment:
-
To rule out problems with your definition of keyNo simply change its definition from variant to string.
You can check whether DLookup is working as expected using the Immediate window in the VBA editor to execute a print statement for the DLookup itself:
? DLookup("GroupN o", "Accounts", "GroupNo<>' 0'")
This should not give a type mismatch error. If it does, leave out the...Leave a comment:
-
Sorry, support for workgroups was dropped some time ago (Office 2007 onwards I think) and is simply not supported at all in any version of Office greater than Office 2003. Office 2013 has its own peculiarities, but the lack of support for workgroups happened well before 2013 was released (which is no comfort at all if you must use your workgroup to continue using your database).
You can of course still use the .mdb database itself, but...Last edited by Stewart Ross; Jul 2 '15, 06:43 PM.Leave a comment:
-
If you have redefined GroupNo as a text field then the Where clause of the DLookup has to be modified slightly. Just as in a SQL Where clause, text values must be enclosed in single quotes:
As you mention that your keyNo variable is defined as a variant type you may find that it is set as a text type when returned from the DLookup. This may...Code:keyNo = DLookup("GroupNo", "Accounts", "GroupNo<>'0'")Last edited by Stewart Ross; Jul 2 '15, 06:36 AM.Leave a comment:
-
Firstly, just run qryJob on its own. A query of that name will be visible in the Query objects list when you open the database window (press F11 to make the database window visible if it has been hidden).
qryJob is the recordsource for the recordset that is being looped through (see line 6 above where qryJob is opened by the OpenRecordset method). If you run the source query directly from the database window you should be able to see...Last edited by Stewart Ross; Jun 8 '15, 04:32 PM.Leave a comment:
-
The VBA code routine looks ok, and as it is working for one invoice I doubt it has anything to do with the bookmarks on your Word template. You need to eliminate the possibility that the recordsource query itself is returning null field values for your vat and totals fields.
Have you checked the contents of qryJob when this occurs? If the relevant totals fields are null in the record concerned they will not be transferred to the Word...Last edited by Stewart Ross; Jun 7 '15, 07:55 AM.Leave a comment:
-
Stewart Ross replied to Cannot open database ". It may not be a database that your application recognizesin AccessAs NeoPa said, network problems almost certainly. Access is not a client/server system, and if you have a look with a packet analyser such as Wireshark at network traffic to and from your PC when running Access you will see just how much data is transferred back and forth for even the most basic of tasks. If network access is interrupted for whatever reason DB corruption can result.
A few years ago I experienced such problems on an internal...Last edited by Stewart Ross; May 16 '15, 05:57 AM.Leave a comment:
-
Observations
1. Commercial stock control systems distinguish between quantity required and quantity on hand. Stock may be available when an order is placed but unavailable when the order is filled, in which case the excess is automatically placed on back order - in effect it is placed in a priority queue for fulfilment once stock is available (like what Z mentioned about using FIFO techniques).
2. Stock is never recorded as issued...Last edited by Stewart Ross; May 10 '15, 07:19 AM. Reason: Additional thoughts on relationships. Clarification on commitments.Leave a comment:
-
The date of creation is not unique and cannot be used to select just one record. Again, I think you need to look seriously at your design as it is almost certainly not in third normal form or higher from what you've told us so far.
-StewartLeave a comment:
No activity results to display
Leave a comment: