Search results

bceo, one of the fundamentals in relational database design is to try and make where possible all PK's in tables as numbers because with number keys, indexing runs much faster, anyway that aside these 2 tables will now join on TBLLicNo.StaffID ---> TBLLicenseNumber.ID so if you open a new...

Now that BVAInet has uploaded the mbd version, I can see the problem(s). First your design of TBLLicNo is not quite right, you should have these fields add new field LicNoPK, select 'autonumber' as the datatype and set it as the primary key. Delete the 2 name fields and add a new field, call it...

unfortuately I don't have a version of Access here at work that can open an accdb file, still on 2003 :mad: bceo, try adding an autonumber field and make it the primary key, then try again David

your query looks correct. Does your table TBLLicNo have a primary key defined? If so which field is it and what data type is it? Also try switching the query to a 'SELECT' query and use the View to see how many records are returned. David

Have you run an append query before successfully, maybe you should check on how an append query works. If your table TblLicenseNumber has values in the Lic #1 field and the staffID field, then it should create records in the new table. David

bceo, what is the name of your original table, is it TblLicenseNumber? If so, make sure on the criteria line for field [Lic #1], you specify Is Not Null David

"I assume the regNo (text) is the license number. If so does that mean I have to entered each licence number as a separate entry into this new table? " bceo, yes regNo was meant to represent the license number. You should be able to populate this new table from your existing staff table. Do you...

"I'd like to basically choose a date range within a text box or something" Yes Barry this is as I thought, whatever contains your textbox or date picker needs to be open when the query runs and look at my ealier reply to see how you set the criteria line in your query, are you familiar with...

bceo, yes use a separate table which will have an autoID (autonumber), staffID (same as your staff table), regNo (text) plus any other fields you deem appropriate such as a date entered, an expired field (to indicate if the vehicle is current Yes/No)etc You can create the records from your...

oh I see you're just using the DoCmd.OutputTo method, you could try using just: DoCmd.OutputTo acOutputQuery, ReportName$, acFormatXLS, , True It's not necessary to open the query and have an error trap such as Private Sub Command12_Click() Dim stDocName As String Dim stLinkCriteria As...

bceo, another approach might be to further normalise your data structure as the relationship of Staff to vehicles is 1 to many, so if you had the staff vehicles stored in a separate table, your query would be much simpler and quicker, to run David

I'm a little confused at where the criteria is coming from, are you trying to use the date picker(s) to set your date parameters? If so then using the WHERE in your Total row is correct but in the Criteria line for your date field you need to use the wizard to locate the controls on your form...

There's probably a number of ways you could manage this, but I'd think about your design and ask yourself the question: is there any value in running and opening the spreadsheet with the intention of discarding the spreadsheet, if the answer is No, then you could mange this by getting the user...

"In the form variable gets the value, but the function (FilterPartNo) doesn't get the stored variable" But how/when are you using/calling the function FilterPartNo? David

you could just try exporting the table to a separate back-up database, run your updates and then you're able to import back in if need be. David

easiest way to add a watch is to highlight the control name and right-click/Add Watch, this will open the Watch window, add one for each control and then put a breakpoint in the code and step through the If statements taking note which route the code takes, look at the values of the controls in...

"And also, how can I be sure that the values are being evaluated properly" Once you know the values in the controls, you'll know if they are being evaluated correctly by the route the code takes in your If statements Drag the yellow highlighted code line back to the beginning of the If...

A simple way is to put a breakpoint in the code after all the controls have been referenced, then highlight the 3 controls, one at a time, hovering the mouse over will usually display the value but obviuosly you can only view one at a time. A better way is to highlight each in turn and...

have you tried stepping through the code and inspecting the values in the controls: Me.txtAccountsStatus Me.txtNet Forms!frmorderentry![tblAllocation_subform].Form.txtSumAmount to see if the values are being evaluated correctly by your If statements David

have you tried simply deleting the sql tables and rerun the code to connect again. David