Search Function on form doesn't return any data from table even thought the info in on the table

[orange]

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.

I am willing to help. Please provide instructions.

eg On the opening form....



Enter wire info, Print and Quit buttons do not describe what I am suppose to do, what I should expect, what the issue to be corrected involves....

[drunyan0824]

My apologies in my eagerness to try something I for got to add the details.

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 wire number of the first record in the query.

If I un-comment lines 15 - 18 all of the messages are what I would expect to get: I get the first wire number, queryComp is one because the two strings are not equal, formSearch comes back with the wire number I enter in the search box and strSearch returns with the wire number that I put in the search box.

I know the variables are being assigned correctly.

The I have reset the data entry parameter to NO.

To me it looks like I am not incrementing through the records on the query when the If else loops runs.

There are no expression in the query.

So either I need to add an expression to query or I need to run a loop that we cycle through all the records in the query.

[Welshgasman]

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.

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

FWIW that link does not use any brackets?

[Welshgasman]

TBH, I do not know why you are using that strComp?
If you remove all that (and walking through the code line by line would show you (as well as that msgbox, which shows the incorrect control/variable?) ) then CTL1984 is found, no problem.?

You are testing wirenumber against strSearch?, so as soon as you enter a new strsearch, it will fail.
If you go to the last record and then enter CTL1984, it would work, but the logic is not sound?

https://docs.microsoft.com/en-us/off...rcomp-function

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

[orange]

drunyan0824,

I have no idea of what you are trying to do with wireCategoy_v3. Perhaps Paul/welshgasman understands.

Why are some controls populated?

Can you lead me or readers through a typical transaction in plain English? I have been asked to do X. This requires 200 ft of wire category Y, size WW.... I want to know if this exists in inventory. I want to reserve this item. I also need ZZ if applicable. This is for Source and Destination whatever.

This is the sort of thing (generically) that might occur in the business. The next thing to insure is that the required entities/tables exist in the database; the attributed/fields are present and named reasonably; the relationships match the business rules of the business. Then in detail, to find if the wire exists, search for that wire, find the related details....

[mike60smart]

Hi

Just looked at your Form "wireCategory_v3"

I applied WelshGasman's Code to your Search Box and it filters to the specific Wire Number.

What I do not understand is WHY are you using this method?

All you need is a Combobox inthe Header of the form which will automatically take you to
the Wire Number selected.

[Welshgasman]

I think you need to review your joins with wireInfor table being that table that you get all records from, and then the others if they are linked.
ATM you have them all as equal, hence half of the wireinfo records are missing from your query.

[drunyan0824]

Yesterday I had to run a network cable from a computer to network switch so that we can set up a virtual switch panel for our master control operators to use at their work station. I needed to run the cat6 network cable from the computer in rack 20 to the network switch that is in Rack 22. Then I labeled the network cable NET9065. The cables is labeled this way because we categorize our wires by what function the cable serves and our labeling scheme is the category that the cable falls under and the next unused 4-5 digit number in our current list of cables. If someone takes the time to scan through the current excel file they might find a number between two existing cables and use that number, but most of the time we just go to the end of the list and increment the last number by 1. I labeled the cable NET9065 because the cable is a network connection between a computer and a network switch vs a cable that from the output of a HD camera that is connected to our router which would have a wire number of HDV9004 because it is the 9004 high definition cable we've installed

With my network cable ran and connected I come to my computer and open my database and go to the wireCategoryQuery_v3 form. Now the reason that form is named wireCategoryQuery_v3 is because I was trying to build this database in small bits to make sure everything was working as I going along. Now, I have my form open and I start filling out the new entry.

I select NET work from the category drop down menu. The wire categories are one a separate table because this is actually the second time I've tried to build this database. The first attempt was atrocious there were 18 individual table, Queries and Forms for each category of wire. So when decided to redo the table I did some research on what the best practices for building a database are. While researching the best practice for building databases I came across this site. The advice I was given from this some users on this site and other resources suggested keeping the the wire categories on their own separate tables because it is best to keep the tables in the database small. And since, at least the way I understood what I was being told, it was best to use separate tables for data fields that aren't directly related to each other and have the data referenced joined to a common table. So since the category of category of wire doesn't have anything to do with who ran the cable there is a separate table for the wire category and who installed the cable.

With the wire category assigned I move on to entering the type of wire that was installed. The wire type is literally what type of we ran. In the example of the computer and network switch the wire type is just cat6 cable. But there are situations we were have to run a cable of GPIs and depending how a device's GPI ports are physically made affects the type of wire we use. If the GPI ports on a device are screw terminal, we just use a simple 3 conductor audio wire. If the GPI ports on a device are RJ-45 terminal we will run a Cat6 cable. In this scenario the Cat6 cable would be categorized as a GPI because the function of the cable is for triggering a GPI and not a network connection.

I enter the source rack into the form so that if there is an issue with that computer and so that in the case of trouble shooting an issue with that computer we can grab a physical copy of the database. yes, we print off a whole new paper copy so we have something to carry around while we are troubleshooting issues. As stupid as it seems having physical copy of all the wire number is really handy when you are trying to figure out why you aren't seeing a studio camera and the output of that camera runs through 10-15 different devices before it hits the router.

Then I enter a brief description of the source that is connected to the cable. In this case it would be MC Virtual Switcher Panel.

Then I enter the destination rack where the cable ends and brief description of the destination device. For this example I enter rack 22 are the destination rack and a destination description of Cisco 2980 switch port 12.

Since there are times where we are waiting for a piece of equipment to be delivered we will install the cable a head of time to make installing the device quicker. Or we might removed a piece of equipment and but leave the cable behind because we don't have time to remove the cable as well as the equipment or we might repurpose that cable later. We decided to start keeping track of whether or not the cable was connected, waiting to be connected or needs to be removed. Since both the source and destination sides can be either connect, not connected, waiting to be connected I created sperate tables for the connection status of the source and destination.

Then I record that I ran installed the cable on 06/30/2022.

Then I record whether or not the installation of this cable requires me to update an autocad drawing.
The installation of some cable requires us to update the labels on a path panel.

Then I click save the record is created and I print my physical copy.

Now two years later we discover that we need to relocate the computer to a different rack and I have to replace the Cat6 cable with a Cat7 cable. Once the computer is moved and everything is connected I open the database and search for wire number NET9065 and I change the wire type to Cat7 and change the source rack to rack 12. Click Save and update the record with the new information and print a new physical copy.

After a bit of time passes and technology changes we no longer need the MC Virtual Switch panel I remove the equipment and all the connected wires. I go back to my database and search for cable NET9065 and delete the record from the database.


That is the extent of what we are doing now. What I am trying to add to our current process is to:

1 Have a method to prevent us from assigning the same wire number to two different cables.
2 Have a method of finding gaps between wire numbers so that we aren't just getting larger and longer wire numbers.
3 Have a method to see if we might have any cables already in a rack that we can use to connect equipment
4 Have a method for seeing what wires we need to be removed or that aren't connected at either the source or the destination
5 Have a method of track if we need to update drawings or labels on a patch panel

To answer some of the question that have been raised in this thread:

In researching the best practices of making a database I was given the impression that it was best to keep tables as small as possible and you should have separate table for data fields that are unrelated or data that doesn't change that often.
A tutorial that I came across gave an example of a database that kept track of various information about college student and the classes they are enrolled in. You might want to keep track of the student's address, the room number of the class and the professor that teaches the class. It doesn't make sense to have the student's address and the professor teaching the class on the same table because the the student's address doesn't have anything to do with the professor teaching the class so each data field get it's own table.

I choose to use a search button because it feels natural to me and because I was trying to minimize the number of forms in the database. I can create one form that can be used to add records, find record, update records and delete records by using buttons.

I don't why everyone can get Welshgasman code to work, but I can't. I need to take look at the first database that I posted to the thread is see if I accidentally deleted something in a query or something else.

I am using strComp because when I was trying to figure out how to compare something that a user puts into a text box against a field in a table it looked like most examples used strComp.
Yes, I get that there is a problem with me IF When statement. I stated in an earlier post that I thought one of the solutions would be finding a way to compare each record in a table against the string of data that was entered by the user. I don't know how to do that.

I am all ears on a better way to search the records but I don't understand why using strComp is bad when I am trying to compare two strings of data.


And yes, I reviewed my joins on my wireInfo table. I saw all my records once I deleted the joins.

[Welshgasman]

You are comparing two strings of data yes, but one is aes1234 and the other is ctl1984. They are NEVER going to match?
If you took the trouble to walk through your code you would see that.?

ANother user here (and in other forums they have crossposted in) are having diffilculty identifying uppercase and lower case values.

Access does not recognise the difference I believe, unless you use strComp()

A quick test in the immediate window appears to confirm this?

? "CTL1984" = "ctl1984"
True

[drunyan0824]

Yes I understand that the string of data aes1234 and the string of data CTL 1984 will never ever under any circumstances be equal. What I am trying to do is take a string of data another wire number from a user and compare that against every wire number in the database so that when a match is found it pulls back the correct record.

That is the whole idea a user enters a known wire number and clicks the search button a record pumps back for that wire number.

So at this point the question is how do I have a user input a known wire number and compare it against the database of wire numbers and pull back the record for that wire number.

strComp will return a value of 0 if the two strings are equal so what I'm trying to do with string compare is when string compare kicks back zero as a result it pulls up the record that the user is looking for

[drunyan0824]

You are comparing two strings of data yes, but one is aes1234 and the other is ctl1984. They are NEVER going to match?
If you took the trouble to walk through your code you would see that.?

A quick test in the immediate window appears to confirm this?

? "CTL1984" = "ctl1984"
True

This is from a previous post I made

To me it looks like I am not incrementing through the records on the query when the If else loops runs.

There are no expression in the query.

So either I need to add an expression to query or I need to run a loop that we cycle through all the records in the query.

I have stepped through my code and I figured out what the problem with my code.

So the how do I take data that a user enters and compare that data against a data field in every record of a table or query?

[orange]

@drunyan0824

Regarding your post #38 --very clear description (more than most would provide). I will try to address some of your comments and concerns at least offer my perspective.

So when decided to redo the table I did some research on what the best practices for building a database are. While researching the best practice for building databases I came across this site. The advice I was given from this some users on this site and other resources suggested keeping the the wire categories on their own separate tables because it is best to keep the tables in the database small. And since, at least the way I understood what I was being told, it was best to use separate tables for data fields that aren't directly related to each other and have the data referenced joined to a common table. So since the category of category of wire doesn't have anything to do with who ran the cable there is a separate table for the wire category and who installed the cable.

Building a database is much like a construction project- at least in concept. You don't start with backhoe, dozer and labourers. The project starts with some stated requirement. After interpretation and possible refinement and the scope is understood, you start some analysis looking for feasible solutions. You review the potential solutions with the client and mutually agree on some preliminary design/blueprint and work schedule. Again you review your design with the client and get confirmation or adjustments and confirmation for actual development. You may have created some prototype to allow the client to "experience" the design. Once the design is accepted, you create a plan for the details; involve the equipment and personnel; and monitor the project.... All this to say - don't start a project by jumping into Access or any database software without a stated requirement and blueprint/data model that has been vetted by the client.

In researching the best practices of making a database I was given the impression that it was best to keep tables as small as possible and you should have separate table for data fields that are unrelated or data that doesn't change that often.
A tutorial that I came across gave an example of a database that kept track of various information about college student and the classes they are enrolled in. You might want to keep track of the student's address, the room number of the class and the professor that teaches the class. It doesn't make sense to have the student's address and the professor teaching the class on the same table because the the student's address doesn't have anything to do with the professor teaching the class so each data field get it's own table.

You have struck upon some key points. Database tables tend to be narrow and long. Spreadsheets tend to be wide and short. (Tend is the point). Database tables are each related to a Subject; different subjects, different tables. Different subjects are related according to the "business rules". In database jargon these are the Relationships that link "related tables". There are different types of relationships and another term Referential Integrity that you will become familiar with. These links are great references and you may want to bookmark them!!
Your student, class, and professor example is often used to illustrate tables and relationships.

Earlier I suggested the stump the model link, because I thought you had familiarity with database. I suggest you step back and work through (45-60 minutes each)one or two of these tutorials from RogersAccessLibrary.
ZYX Laboratories ,
Class info system,
Catering Business,
Widgets to actually work through the design of a data model based on a business description. The data model becomes the blueprint for the physical database. You will learn about entities/tables and attributes and relationships - and what you learn can be used with any database.

Because of the details in the steps you provided in #38, I think you would have much more confidence in your design and your own ability if you worked through 1 or 2 of the tutorials, then applied what you learned to your own database. That is my advice to you at this point.

My suspicion is that you have jumped into physical Access too soon and not aware of key database concepts. I feel you are hoping Access will do something magical for you. It won't. Access will do what you tell it or what it thinks you told it. Bottom line is you need to do the analysis and design to get your model designed and tested, then move to physical database. It will save you time in the long run and you will learn a lot of database concepts and techniques and you will gain confidence. I would also suggest you learn some vba and be less reliant on the "macros" that Microsoft has given us.

[drunyan0824]

First off I don't think I've jumped in with backhoes and construction equipment.

Before I started programming this database I thought about all the categories that we normally keep track of in our Excel spreadsheet and because of limitations and issues I've had with Excel and duplicating entries I wanted to move over to access because I realized it what address some of the issues I have with Excel.

I am not expecting Excel to do anything magical for me. I have experience programming in c++, and some other programming languages so I fully get that something is can only function as well as it is programmed.

I will take a look at the tutorials you posted me and work through some of them and see if I can come up with the better design or a better understanding of how databases are designed and what all the database can do.
But to be quite Frank with the exception of this issue the database that I have created is doing everything that I wanted to do and yes along the way I thought of other things that it would be nice to to have the database do.

The biggest issue that I'm having right now is I would like to get away from having to use macros I would love to know how to program in VBA.

Through doing my own troubleshooting and playing around and access I was able to figure out why only some of my records were showing up in a particular query and then I figured out how to fix that issue.

Yes I have problems with the syntax of one of the commands because because I assume the parentheses were part of the argument which I think is extremely understandable considering there are some VBA functions and commands that require parentheses and some VBA commands that don't

Right now one of my biggest issues is I have been told repeatedly that I need to step through my code line by line to see the mistake after after I made a post where I acknowledged the problem.

So it would be great if somebody on this thread could acknowledge the fact that I realized the problem with my string comparison and my if while loop instead of just telling me to look at the code and asking me why I'm using string comparison.

So at this point my question is how do I take that macro I programmed that took information from a text box in compared it against a data field on a table for every record and returned a result and program that into VBA

I know I've probably shot myself in the foot with my last couple of posts but I don't think my question is that hard. It's a simple request to just figure out how after I've taken one string in compared it to one record do I move on to the next record to compare the string again until I've found a match

[orange]

@drunyan0824

OK you have made your point! You have C++ experience and you know that it is your programming that determines the quality of code matching requirement. So the issue is getting your logic coded into vba. Your current database has the info you need and relationships seem to be working as you need them.

[orange]

Can we take a specific issue and try and get it working?

I have your database: Tables and Reationships Test for Wire Database_Working_Copy.accdb

I ran the Convert all macros to vba and also add error handling.

I have looked at one of the subs and made some comments that may help with vba.
My comments are those beginning '--

Private Sub searchBtn_Click()
Dim tstVariables As Integer

 
'--you should explicitly dim variables with datatype, otherwise they are all variants
'--not a major issue, but is considered best practice
Dim strSearch As String
Dim queryComp As Integer
 
 
 '--Me represents the current form 
 '--you identify the control on this form--> so Me.formsearch
 
 strSearch = Me.formSearch
 
 '--Access  doesn't know which wirenumber you are referring to
 '--it appears to use the wirenumber on the current record
 
 
 
 queryComp = StrComp([wireNumber], [strSearch], [vbBinaryCompare])
'--strComp returns an Integer so the " is not needed
'--
'--in Access vba strings are enclosed in double quotes   eg mystring ="someChars"
'--                    dates are enclosed in hash/octothorpes eg myDate =#07/01/22#
'--                    numbers have no enclsoing chars   eg myNumber =85
'--
If [queryComp] = 0 Then
'--you are testing if querycomp is 0 or Not 0, no need for ElseIf, just use Else
   
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

Note: If I am on record 1,which is category FIB, wirenumber AES1234, and type AES or aes into the formsearch control, the msgbox shows AES1234.

If I go to record 2 Category DAT wirenumber SDV1234, and type AES or aes into the formsearch control, the msgbox shows SDV1234.

If I go to record 3 Category AUD wirenumber HDV1234, and type AES or aes into the formsearch control, the msgbox shows HDV1234.

So, it seems Access is using the wirenumber of the current record.

My guess is that if you want to find a record in your table, you need a form with only a search box and a button. You enter a search term, click the button and if a record is found matching your search term, you open a new form with the specific record your found via search.

In your database, the form displaying records in your table is already open and Access appears to use the current record info to populate the msgbox. It is not searching for a record based on your strSearch.