Results 1 to 12 of 12
  1. #1
    Join Date
    Jun 2013
    Posts
    81

    Question Unanswered: MS Access 03 - complex update/select query

    I've been scratching my head for a while over this one. Thought I'd got it cracked today, but no .

    I'm working on building a database to house our equipment records. Most of our equipment is made up of sets of equipment e.g. a bag of weights will contain 13 weights and would have a set ID of something like Metric 22. Details are stored in 3 main tables tblSet (which houses generic information), tblWeight (which houses specific weight information) and tblResult (which houses test results).

    I'm currently trying to set up a user interface to allow test results to be entered into the system, which is proving problematic.

    To help explain (I hope) I've attached a screen dump of tblResults and a mock up of what I would like the form to look like (always open to suggestions for improvements though).

    Right, this is proving a tad complex to explain, so I'll try it in steps
    1. User clicks the "Enter Test Results Button" on frmSet
    2. Access opens frmResults
    3. Access identifies the SetID from the record already open
    4. Access autopopulates frmResults with the list of weights in the set (including the serial numbers and weight quantities which are taken from the linked records in tblWeight)
    5. User enter test result details in the blank spaces on the form which are appended to the end of tblResults


    I've been wracking my brain on how to set this up but nothings quite gelling for me, so any advice would be greatly appreciated.

    I have thought about some sort of select and append query, not sure how it would work though. I've read around subqueries, but haven't got my head around them yet, certainly not enough to apply it to this. My other thought was a form which is bound to tblWeight and displays all weight records linked to the Set ID. I then added unbound text boxes to allow users to enter the test results details, which I was going to append to tblResults using a save button (building the SQL into the code behind the button). But as I'm working with a continuous form to display all weight records, I understand you can't use an unbound text box.

    I hope I've provided enough information and haven't confused you with my explanations. Any advice you could give would be greatly appreciated.
    Attached Thumbnails Attached Thumbnails tblResults screen dump.jpg   Test Results mock up.jpg  

  2. #2
    Join Date
    Jun 2013
    Posts
    81

    Question

    I think I may have worked out a method, but I'm struggling to work out how to apply it. The new idea/method is:

    • Set up a form based on a query which joins tbleWeight and tblResults, so that the user can see the serial number and weight quantity rather than a meaningless autonumber ID for the weight (I've set this up and it seems to work quite nicely - even better I can enter new records into the results table)
    • Create a command button on the set form to open up the test results entry page
    • In the background to the command button use VBA code to:
    • identify all weights in the set (by weight ID number)
    • create an insert query to create new records in the results table, entering the test date, officer number and weight ID
    • Open test results form, filtering the results to only show entries with the test date just entered - users can then enter actual test results, but all generic information will already be input


    Having thought about it, I suspect that I will need to create some sort of select query to ID each weight ID then use a loop with the insert query, so that the insert query only worries about one record at a time. If that makes any sense

    As you can probably work out from my drivel (for lack of a better word) I'm struggling to get my head around this. Any suggestions would be greatly appreciated

  3. #3
    Join Date
    Jun 2013
    Posts
    81

    Question SQL codes

    I think I''ve cracked how to set this up now, but I'm having issues with my SQL coding (in VBA). I cannot work out what on earth I'm doing wrong (bound to be something really silly as well - it was the last time a ) in slightly the wrong place, took me hours to spot it lol). I'd appreciate it if someone could take a look at my code see if you can spot what I've done wrong.

    I've set up a command button on my main form. The idea is that when the user clicks it, the system will identify any and all weights associated with the set record currently open (approximately 13 in most cases), insert a new test result record for each weight (which includes the test date - prompt - and the officer ID number - prompt at the minute). The system will then open the test results form (not written this bit yet) and set a filter to only show the records for test date just entered.

    The code (everything attached to the button) is:

    Code:
    Private Sub CmdWeightSetTestResults_Click()
    
    Dim strTestDate As String
    Dim strOfficerNo As String
    Dim SQLResponse As Integer
    Dim strSetID As String
    Dim strSelectSQL As String
    Dim LDate As Date
    Dim dateResult As Date
    Dim strInsertSQL As String
    
    'prompt user to input results entry date
    LDate = Date
    strTestDate = InputBox("Enter the test date", "Test Date", LDate)
        If StrPtr(strTestDate) = 0 Then
            'user cancelled the application, exit sub
            MsgBox "You have closed the test results entry system"
            Exit Sub
        ElseIf Len(strTestDate) = 0 Then
            'user clicked ok without entering a date
            MsgBox "You have clicked ok without entering a valid test date, the test results entry system has " & _
            "been closed"
            Exit Sub
        End If
    'prompt user to enter their officer number - !!!!THIS NEEDS UPDATING TO AUTOMATICALLY IDENTIFY FROM LOG ON!!!
    strOfficerNo = InputBox("Enter your officer number", "Officer Number")
        If StrPtr(strOfficerNo) = 0 Then
            'user cancelled the application, exit sub
            MsgBox "You have closed the test results entry system"
            Exit Sub
        ElseIf Len(strOfficerNo) = 0 Then
            'user clicked ok without entering an officer number, exit sub
            MsgBox "You have clicked ok without entering a valid officer number, the test results entry system " & _
            "has been closed"
            Exit Sub
        End If
    'warn users this action will create several new records and you cannot undo changes, with option to cancel
    SQLResponse = MsgBox("This action will create several new test results records.  Once you click yes you " & _
    "cannot use the undo command to reverse the changes.  Do you want to continue?", vbYesNo)
        If SQLResponse = vbYes Then
            'user is happy to continue.  Create SELECT query to ID relevent WeightID's
            'first set strSetID and dateResult
            strSetID = Me.SetID
            dateResult = Format(strTestDate, "yyyy-mm-dd")
            'create select query as string
            strSelectSQL = "SELECT tblWeight.[WeightID " & _
                           "FROM [tblWeight] " & _
                           "WHERE (((tblWeight.SetID) = '" & strSetID & "'));"
                        Debug.Print strSetID
                        Debug.Print strSelectSQL
                        
                        'note: replacing the select query with a hardcoded "3" it works (need to alter data & _
                        'type for office number, got it set to "number" but if I want to use our FLARE codes & _
                        'it needs to be text.  Saying that, if Access can pick up the officer code from the & _
                        'log in, it makes no odds and it can stop as an auto number
            'create insert query
            strInsertSQL = "INSERT INTO tblResultWeight (ResultDate, SetID, WeightID, OfficerNumber) " & _
                           "VALUES ('" & dateResult & "', '" & strSetID & "', " & _
                             "'" & strSelectSQL & "', '" & strOfficerNo & "');"
            Debug.Print dateResult
            Debug.Print strOfficerNo
            Debug.Print strInsertSQL
            CurrentDb.Execute strInsertSQL
            
    Exit Sub
    End If
            
    End Sub
    The error code I get is

    "Run-time error 3075: Synatx error (missing operator) in query expression "SELECT tblWeight.WeightID FROM [tblWeight] WHERE (((tblWeight.SetID = 'Metric 22'));"

    The bit of code in red, is the bit Access highlights when the error code pops up. To make it easier to spot I've put the bit of code I suspect is the issue in bold.

    The debug information the code prints into the immediate window shows:

    • Metric 22 (this links to strSetID and gives the expected result)
    • (SELECT [tblWeight].[WeightID] FROM [tblWeight] WHERE [tblWeight].[SetID] = 'Metric 22'); (this links to strSelectSQL)
    • 30/01/2014 (this links to dateResult and gives the expected result)
    • 469 (this links to strOfficerNumber and gives the expected result)
    • INSERT INTO tblResultWeight (ResultDate, SetID, WeightID, OfficerNumber) VALUES ('30/01/2014', 'Metric 22', '(SELECT [tblWeight].[WeightID] FROM [tblWeight] WHERE [tblWeight].[SetID] = 'Metric 22');', '469'); (this links to strInsertSQL)


    If I hardcode a weightID in place of the strSelectSQL statement the code works, but for some reason I can't get the SELECT statement to work. I know I'm going to need to add a loop in (when I work out how) as there are 13 weight ID records linked to strSetID (Metric 22). Could the fact that I haven't included the Loop yet be the cause of my issues?

    Any advice would be greatly appreciated. I've been battling this for nearly 2 days now. My hair is starting to get a little thin lol.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    "Run-time error 3075: Synatx error (missing operator) in query expression "SELECT  tblWeight.WeightID FROM [tblWeight] WHERE (((tblWeight.SetID = 'Metric 22'));"
    you are missing a closing bracket
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. There is a missing closing bracket in the SELECT query:
    Code:
    "SELECT tblWeight.[WeightID] "
    2. The Fields OfficerNumber and WeightID are declared as Number in the table definition, while you enclose the values for these fields in quotes which should only be used for Text or Memo values. Similarly, date values (dateResult here) should be in mm/dd/yyyy or yyyy-mm-dd (ISO) format when used in a query.

    3. You cannot include a string containing a subquery and hope that it will be executed in an INSERT query with the VALUES syntax: this is only possible when using the INSERT INTO ... SELECT ... syntax. As it is, the query string strInsertSQL looks like:
    Code:
    INSERT INTO tblResultWeight (ResultDate, SetID, WeightID, OfficerNumber) VALUES ('30/01/2014', 'Metric 22', 'SELECT tblWeight.[WeightID FROM [tblWeight] WHERE (((tblWeight.SetID) = 'Metric 22'));', '469');
    Try (aircode):
    Code:
    lngWeightID = DLookUp("WeightID", "tblWeight", "tblWeight.SetID = '" & strSetID & "'"
    strInsertSQL = "INSERT INTO tblResultWeight (ResultDate, SetID, WeightID, OfficerNumber) " & _
                   "VALUES (#" & dateResult & "#, '" & strSetID & "', " & _
                    lngWeightID  & ", " & strOfficerNo & ");"
    Or:
    Code:
    strInsertSQL = "INSERT INTO tblResultWeight (ResultDate, SetID, WeightID, OfficerNumber) " & _
                   "SELECT #" & Format(dateResult, "mm/dd/yyyy") & "#, '" & strsetid & "', WeightID,  '" & strOfficerNo & "' " & _
                   "FROM tblWeight " & _
                   "WHERE tblWeight.SetID = '" & strsetid & "';"
    Have a nice day!

  6. #6
    Join Date
    Jun 2013
    Posts
    81

    Smile Getting there

    Thanks for your comments both I really appreciate the help.

    Lol its amazing how easy it is to miss brackets . Having said that I've added it in and I still gets the same error code lol. Never mind I've deleted it now.

    Sinndho I had originally got the SELECT string in as part of the INSERT query (basically switched it for strSelectSQL in the SQL code), but when I started having issues I seperated it to try to make it easier to read. Guess that wasn't such a great idea huh. Ho hum I'm learning - slowly.

    Clearly I haven't really understood INSERT INTO ... SELECT queries. As I'm currently understanding them, the code you have very kindly drafted out for me (the last bit) seems to be saying to select all of the fields (that match strSetID for the SetID field)) from tblWeight, which isn't possible as most of the fields aren't in that table. Now, I've tested the code and it works (ish) so I'm obviously wrong, do you know of any good online guides which could help me get my head around it?

    As I said the last bit of code you drafted works for the most part (thank you so much, you are a hairline saver if not a life saver ). The only issue I seem to be having with it is the dateResult field. For some reason its reading in the table (and immediate window - I've left the debugging code in place) as 00:00:00. The field properties (in the table) are set to general date. As an experiment I've switched it to short date and it then reads 30/12/1899. I'm sure I've had issues with dates before, but I'll be wotsited if I can remeber how I fixed it (probably with your help ). Any ideas what I've done this time?

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    30/12/1899 is 0. Access / JET stores dates as a decimal number.
    so Id guess you are not setting the date in the control or variable

    FWIW:-
    the integer bit stores the number of days from 30/12/1899. (ie day 1 is 01 Jan 1900)
    the decimal bit is the proportion of the day. so .5 = 12:00:00, .75 = 18:00:00,0.0000115740 is 00:00:01 (or 1/(24*60*60))

    i'd suggest you develop your query using the SQL view of the query designer
    prove the query works there, then one proven adapt it to the needs of the program. developing a query in VBA code can eb incredibly frustrating. once you know the query works then you can plug it into your covde (or parameterinse the query and call it from code)
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jun 2013
    Posts
    81

    Lightbulb Fixed it

    Hahahaha I've worked it out. I'm a bit of a silly billy - when I added the new bit of code (thanks again Sinndho) I'd quite cleverly deleted the little bit of code which actually identified dateResult, so the first example of dateResult in the code was in the query ooops.

    I've fixed it now and it now works beautifully, feeling extremely sheepish though lol Heavens only knows what made me think of it, I wasn't even working on it.

    Thanks for the response Healdem, I really really appreciate your input. Next time I develop a query in code (bound to happen) I might try your suggestion first, see if I can get it to work a little quicker that way.

    Once again thank you both for taking the time to help me. Unfortunately I don't know anyone who understands MS Access, VBA, SQL and in some cases computers in general. It can be very frustrating trying to work out what you've screwed up this time, when theres no one to bounce ideas off or ask for help. So I really appreciate being able to come here and get help. Drinks are on me

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by SamJP View Post
    Thanks for your comments both I really appreciate the help.
    You're welcome!
    Quote Originally Posted by SamJP View Post
    Lol its amazing how easy it is to miss brackets . Having said that I've added it in and I still gets the same error code lol. Never mind I've deleted it now.
    You can drop the square brackets entirely provided that you follow a few rules when naming the database objects:
    - Every object (table, Column, Index, ...) starts with a letter (A-a to Z-z).
    - The name of every object only contains alphanumeric characters (A-a to Z-z, 0 to 9) plus the underscore character (_).
    - Don't use accented characters.
    - Don't use reserved words (Access Help and How-to - Microsoft Office Reserved Words in Microsoft Access - UtterAccess Discussion Forums) for naming an object.
    Quote Originally Posted by SamJP View Post
    Sinndho I had originally got the SELECT string in as part of the INSERT query (basically switched it for strSelectSQL in the SQL code), but when I started having issues I seperated it to try to make it easier to read. Guess that wasn't such a great idea huh. Ho hum I'm learning - slowly.

    Clearly I haven't really understood INSERT INTO ... SELECT queries. As I'm currently understanding them, the code you have very kindly drafted out for me (the last bit) seems to be saying to select all of the fields (that match strSetID for the SetID field)) from tblWeight, which isn't possible as most of the fields aren't in that table. Now, I've tested the code and it works (ish) so I'm obviously wrong, do you know of any good online guides which could help me get my head around it?
    There a 2 possible syntaxes for an INSERT query, plus a special SELECT query that inserts the selected values in a new table.
    Code:
    INSERT INTO TableName (Fields list...) VALUES (litteral values list...);
    All values must be litterals or be the result of a function( e.g. Now(), UCASE(something), etc.).
    Code:
    INSERT INTO TableName (Fields list...) SELECT ...
    In this case the SELECT can be a proper SELECT query (SELECT Field1, Field2, ... FROM SomeTable WHERE <Condition>) or it can be a mix of fields from a SELECT query, litteral values and/or return values from functions. Contrarily to the standard SQL syntax, there must be a FROM close in the select expression (in SQL Server for instance, you can use: INSERT INTO Table1 (Field1, Field2) SELECT 'A', 'B'; without FROM in the select expression).

    The third type does not use the INSERT INTO instruction:
    Code:
    SELECT Field1, Field2, ... INTO NewTable FROM TableName
    In this case, the Table NewTable will be created when executing the query.

    All these are clearly explained in Access help. Open the Table of Contents then select Microsoft Jet SQL Reference, DML.
    Have a nice day!

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as ever the important lesson form this is when you are getting odd errors or errors you can't understand why then look at the actual SQL beeign sent to the sql engine

    its a heck of a lot easier to understand what is going on by looking at the SQL.
    ferinstance I guess you would immediately have spotted that the date was bolluxed

    at the very least dispaly sql to a msg box, but better yet learn to use the integrated debugging tool built in to the IDE. persoanlly I can't see how ANYONE adding code to their Access project can seriously develop stuff without using the debugger. for most people its worth spending a day or two on how to use the debugger. that day or tow will be repaid many times over even on a fairly modest project.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Jun 2013
    Posts
    81
    Thanks both that really helps.

    Think I need to sit down and go through SQL and debugging a bit more, sounds like it can do a lot more than I know about. I've gone through information on debugging a couple times previously but it doesn't seem to have sunk in. I'll have another dig see what I can find.

    Thanks again for your help I really appreicate it.

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •