Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 52
  1. #16
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

    So ideally the way the database would work is that after I was finished running a cable I'd open the database and add all of the information about the cable that was just ran and add it to the database.
    Do you not have inventory such that you can check before you run cable for a job? You would know what cable(etc) existed and you could enter your projected usage in the database, then update with actual usage. If you simply use the database to report what you have done (after the fact recording), I suspect you may be missing many of the advantages of database. But you know your business and environment better than any user.

    There are several articles in various formats in the Database Planning and Design link in my signature.

    But I can't get the search function to return anything for about half the records in the table. I was just using AES1234 and CTL1984 as an example.
    My guess is that you have jumped into physical database with out thorough analysis and testing. That half of the records are not being found in a search indicates a big issue. Work on small pieces of the task, mock it up, test it, then incorporate into the bigger picture.

    You may find this stump the model of interest.

  2. #17
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Currently we just have an excel file that we use for both tracking inventory and adding the new cables. Recently I ran into an issue where I duplicated a bunch of wire number for an entire project and that is why I started to create a database in access. Ultimately my end goals with the database is have a way to check for available wire numbers before we start a project so we can avoid duplicating wire numbers, to catch a duplicate entry before we add the record to the inventory, to track and make sure that all documentation such as wiring diagrams and labels are updated with the inventory.

    Thank you for the stump the model I will check it out shortly.

  3. #18
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    After looking at the stump the model post and some of the articles in your signature I was able to set up a simple query that compared two strings. The first time I created the query I just set the comparison between the wireNumber field and a string that I typed in as the second string. Then I was able compare a string entered into a text box against the wireNumber field.

    Now I am trying to figure out how to compare a string entered into a text box against the wireNumber field.

    This is the code I have but I keep getting a syntax error and I can't figure out where my syntax is wrong.

    Code:
    Private Sub formSrcBtn_Click()
    
    'declare variable for text box data and set string to compare against table
    Dim strSearch
    Dim queryComp
     
     strSearch = ([formSearch])
     queryComp = StrComp([wireNumber], [strSearch], [vbBinaryCompare])
     
    If [queryComp] = "0" Then
       
        DoCmd.OpenForm("wireCategoryQuery",,,WhereCondition:="wireNumber=" strSearch)
    
    
       
        
    End If
        
      
     
     
     
     
    End Sub
    I am basing the DoCmd line on this

    https://docs.microsoft.com/en-us/off...i/access.docmd

  4. #19
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    You need to concatenate your variable with the criteria name.
    Plus as it is a string you need a single quote or double double quotes if a single quote couldbe in the string? In your case I do not believe that is the case.

    So perhaps
    Code:
     DoCmd.OpenForm("wireCategoryQuery",,,WhereCondition:="wireNumber='" & strSearch & "'")
    If you use dates, you would need # and numerics need nothing.

    I would also be specific about the control and use Me.FormSearch.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #20
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Okay, I made the changes you suggested about the DoCmd.OpenForm, but when keep getting a syntax error. The first error that was kicked back was about missing an expected = and then all the error after that are just generic error messages about syntax.

    Then I tried to use the Me.formSearch and I am pretty sure that I used it wrong.

    My code when I tried to do Me.formSearch was

    Code:
    Private Sub formSrcBtn_Click()
    
    If formSearch <> "" Then
        
        strSearch = "Select * from wireInfo='" & formSearch & "'" & ""
        Me.formSearch = strSearch
        Me.Refresh
        
    
    
    
    
        
    End If
        
      
     
     
     
     
    End Sub

  6. #21
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I have no idea what that last code is meant to do -( So yes, you were using it wrong.
    They are not the changes I was talking about?

    In that code above you take the value of the formsearch control, append it to some text and then assign it back to the form control?
    What is that going to accomplish?

    My comment was for the OpenForm statement. You need to concatenate the formsearch control value with the field in question to open the form for that wire number.
    I personally never use the named arguments (cannot remember the excat names), always positional, and intellisense helps with that.

    Code:
    DoCmd.OpenForm "wireCategoryQuery_V3",,,"wireNumber='" & strSearch & "'"
    Whether you got the syntax correct or not, (and that above is correct, I tested it in the imemdiate window), you would not open to that record, as data entry is set to Yes for that form, so you can only add records. Data entry is badly named, it means you can only ADD records when the form is opened, so even when I located the correct record, which I ensure was one in the query which is the source for the form, it still opened to a blank record. That mode might have it's place, but I have never used it.

    Plus the formname does not exist?, it has a _V3 suffix. ?

    Do yourself a favour and adopt some sort of naming convention, so you can identify what is what in your code.

    I myself use prefixes of
    tbl for Tables
    frm for Forms
    rpt for Reports
    qry for Select queries
    qryA for Append queries
    qryU for Update queries
    qryD for Delete queries

    etc etc. You can use whatever prefixes you like, but be consistent. Using the ones above, most people would know what each object was (well perhaps not the query types, but at least that they were queries).
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #22
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    I will set the data entry on the form back no and try to run the code again. I have no doubt that the code you are giving me is correct, but when I try to run the code I get a compile error message.

    Maybe I should just dump all of the queries and forms that I have made a start from scratch again. Then I can set up a query and form for adding wires into the database and a separate query and form for searching and deleting records.

    One step forward, two steps backward

  8. #23
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Personally, I would have one form for all.
    No, not all code I write, works first time, but as I mentioned, I tested it in the immediate window, with a value I know is in the query for the form.
    Upload a pic of the compile error.
    You have your work cut out for you TBH.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #24
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Error 1 is the error message that I get when I move on to a new line when after typing the Do.Cmd line

    Click image for larger version. 

Name:	Error 1.png 
Views:	13 
Size:	10.5 KB 
ID:	48179
    error 2 is the message I get when I type an entry into the box and click the search button

    Click image for larger version. 

Name:	error 2.png 
Views:	13 
Size:	7.5 KB 
ID:	48180

  10. #25
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Use the openform code I supplied. Make sure you use the correct control name.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  11. #26
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    I used the openform code you supplied and I am now longer getting the error messages. I was thinking that I need the parentheses because the were showing up in examples I was seeing online and because when I was typing out the that command the parentheses show up in the little box that shows all the arguments for a command.

    But I am still not pulling up any record when I try to search for a wire number. As far as I can tell, my logic is sold on my IF Else statement. I added a message box prompt in the event that the first statement is not true.
    Code:
    
    
    Private Sub searchBtn_Click()
    Dim tstVariables As Integer
    
    
    Dim strSearch
    Dim queryComp
     
     strSearch = ([formSearch])
     queryComp = StrComp([wireNumber], [strSearch], [vbBinaryCompare])
     
    If [queryComp] = "0" Then
       
    DoCmd.OpenForm "wireCategoryQuery_V3", , , WhereCondition:="wireNumber='" & strSearch & "'"
    ElseIf [queryComp] <> "0" Then
    
    
    tstVariables = MsgBox(wireNumber, vbOKOnly)
    'tstVariables = MsgBox(queryComp, vbOKOnly)
    'tstVariables = MsgBox(formSearch, vbOKOnly)
    'tstVariables = MsgBox(strSearch, vbOKOnly)
        
    End If
    
    
    End Sub
    The only time the first statement is true is when I search for AES09876 because that is the first wire number on that show up on my wireCategoryQuery.

    Which leaves me with two problems:
    1 I need to find a way to got through my wireCategoryQuery entry by entry.
    2 I need to figure out why my wireCategoryQuery isn't showing all the entries in my wireInfo table. I can see all of the entries on my wireInfor table when I create a new black query looking at the wireNumbers in the wireInfo table.

  12. #27
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    If the form is based on your query and NOT Data Entry = Yes, then you will see all the records in the query.
    As for misssing records in query, that will because of a join.
    My PC is currently making it's monthly system image, so unable to view the query.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #28
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How about posting your latest version of your database as a zip?
    Also include an example of a query with a wirenumber that you are currently having an issue with.
    Please provide as much detail as you can.

  14. #29
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Quote Originally Posted by Welshgasman View Post
    If the form is based on your query and NOT Data Entry = Yes, then you will see all the records in the query.
    I am trying to remember how I made my form. I am pretty sure that I did base it off the query, but I can't remember exactly. When I look in the record properties of the form I see the record source is set to wireCategoryQuery so I am assuming that I based the form of the query.

    As for misssing records in query, that will because of a join.
    It's funny, just a few minutes before you last response I was thinking about why I was seeing all the records when I created a new query with just the wireNumber field and on a whim I decided to see what would happen if I deleted the other table from the query. All of the records showed up in my query once I deleted the other tables and just used the fields from the wireInfo table.

  15. #30
    drunyan0824 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2022
    Posts
    82
    Tables and Reationships Test for Wire Database_Working_Copy.zip



    This is the the most current copy of the database.

    It looks like when I click the search button on the wireCategoy_v3 form the logic of my IF Else statement run correctly. However the only time that the first statement is true is when I enter the wire number of the first record in the wireCatergoryQuery.
    I can now pull up the record for AES1234 when I search for that wire number. When I try to search for wire number CTL1984 no record is pulled up and I get the message box that just show the wireNumber


    The I have reset the data entry parameter to NO.

    I don't have any expression in the wireCategoryQuery but I was thinking of trying to do another string comparison in the query that

Page 2 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 07-05-2015, 11:11 PM
  2. Form doesn't add data to table
    By wardw in forum Forms
    Replies: 4
    Last Post: 05-22-2013, 03:29 PM
  3. Replies: 6
    Last Post: 10-25-2012, 04:13 AM
  4. Replies: 9
    Last Post: 02-15-2011, 03:05 PM
  5. Replies: 3
    Last Post: 01-31-2011, 11:47 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums