Adding Records and Searching Records on same form

[jo15765]

Is there a way to take the switchboard out of this database and make it to where, you can add new data, and search for existing data, all from the same form?

[pkstormy]

Yes but I wouldn't recommend doing this on your 'Main Menu' or Switchboard form. Ideally, you want to keep your 'Main Menu'/Switchboard form unbound to any recordsource (which is why I don't like the Switchboard since it's bound to a table). I like to keep my Main Menu form unbound from any table with only a few buttons. For example, a button to 'Add Customer' or a button to open a 'Search' form.

For examples see (in the code repository):
https://www.accessforums.net/code-re...hods-7689.html
or
https://www.accessforums.net/code-re...ally-7688.html
or
https://www.accessforums.net/code-re...ames-7680.html

You could also add an unbound field on your Main Menu form and then in the AfterUpdate event have it open your data form to the record.
Ex:
Private sub MySearchIDField_AfterUpdate()
Docmd.openform "MyDataForm",,,"[IDField] = " & me.MySearchIDField.value & ""
End sub

or on your data form have an unbound field and in the AfterUpdate event
me.IDField.setfocus
docmd.FindRecord me.MySearchIDField.value

or to open the data form and go to a new record
Docmd.openform "MyDataForm"
Docmd.gotorecord,,acnewrec

Otherwise what typically happens is that your 'Main Menu' no longer becomes a 'Main Menu' but instead becomes a 'Search' type form (and you end up adding another search by field, and then another, and so on.) Again, just my preference but I like to keep my Main Menu as the 'Central Station' so to speak in which to direct where the user wants to go in the program (ie. having no clutter and only a few buttons such as: Add Customer, Find Customer, Reports, Exit, etc..) I've found this seems to be the easiest/quickest way for new users to learn the program, especially if the program is complex (I try to make it so users are directed to other forms but can always go back to the Main Menu as a starting point.) The less cluttered the form is, the easier it is to learn.

[jo15765]

What if I removed the main menu/switchboard, and still had the form, pull from the query, would it be possible to then add records from the form? Basically keep the db exactly the same with the exception of removing the switchboard?

[pkstormy]

I'm not sure I understand what you mean but it sounds like you want the data form to be the Main Menu. Keep in mind that a data form (which returns all records) opening first when the db opens is not the route I'd go. Typically what I like to do is make my data form open so it opens to a single record versus all the records.

ie.
Docmd.openform "MyDataform",,,"[IDField] = " & varIDVariable & ""

This then opens MyDataForm to a single record versus opening it to all the records. If your database grows to 100,000+ records (plus a bunch of relational tables), having a form open with all the records can be taxing and will slow down performance of the db.

There's nothing to say it's wrong to open a data form first when the db opens (providing you don't have a huge recordset), but ideally (and my personal preference) is to have the data form open to only a single record.

Example:
Main Menu form - has a button called: Find Person
Find Person button is clicked - opens Search form (which has all the fields to search/find a person) - see the alphabetical example: https://www.accessforums.net/code-re...ally-7688.html

Person is found on search form - after they click on that person in the search form, opens the data form to that person's record. On the data form there is also an 'Add New Person' button to allow the user to add a new Person on the data form (ie. docmd.gotorecord,,acnewrec).

[jo15765]

I want to be able to open the database, and it takes me to a single record on my form, and if I need to see the other records I can either click the next button to go to the next record or search for the record.

The search I want to take place, and it has worked on smaller databases I have designed, is to create a combo box that when the name (or whatever I am searching for) is typed into the combo box, it takes me directly to that record.

In this instance, I was trying to have the combo box search multiple fields, which is the problem I was running into because if I searched for 99 for example, if that was not in the price1 field, it would not return the value I was saerching for. I was also exploring the option (with much help from people on this site) of then using a command button with a line of VB code that tells the button where to search (fields1 - fields9) when pressed.

The problem I am running into tho, is I want to search through multiple fields, as opposed to just one field, which is what I have become comfortable searching. What I had been trying was to type a price into the combo box and have it search through all of the price fields, until
the price I am wanting to find is located.

What I am after may not even be possible, but I am trying to keep the database all one level, ie open the database to a form that the users will input data into as well as search off of, as that is what they have been using and are comfortable with.

[pkstormy]

Something like the attached might work for what you want. After the 'SearchPrice' value is entered and the 'Search' button is pushed, it changes the recordsource of the form to a query which has OR criteria in it for all the price fields. When the 'Clear' button is pushed, it changes the recordsource back to just the table (or it can be to another query). You could also add a 'Search Price 1' button, 'Search Price 2' button, etc... where it sets the recordsource to whatever query that has criteria under just the appropriate price field (ie. qryFilterPrice1, qryFilterPrice2, etc..). Use this as an example to apply to your db.

[jo15765]

Few questions, when creating your unbound combo box, which option did you choose....1 2 or 3, like the option of I want cbo box to look up values (1) etc.

Also, I may have input the information wrong into my query, but when I Hit the search button, I got a pop up asking me for price1 price2 etc. The only difference that I see now is that I put the formula [Forms]![frmMain]![SearchPrice] in the Criteria for each of my prices, instead of going down one field for each price.

The bad part is, I am working on this at work, and don't have access to this database while I am at work to troubleshoot, so I am hoping that I didn't do a simple small mistake and that is why it isn't searching correctly.

[jo15765]

I fixed everything except for the fact that when I input the price into the search box, and hit the search button, I get a popup asking me to input the price? Why is it asking me for a price, if I input the price into the search box?

[pkstormy]

You're getting the error because you designed a different form called frm2 but you still use the same query (qryFilterPrice) and you didn't change the criteria in this query based upon your new frm2 form name. The criteria in the qryFilterPrice is still =Forms!frmMain!SearchPrice when it should be =Forms!frm2!SearchPrice (for each of the OR criteria). (also note that qryFilterPrice is based upon Table1 and not the new table you created called tbl2.) (also note that your query called: qrySearchAccount has criteria in it based upon: =[Forms]![tbl2]![SearchAccount]. This should most likely be =[Forms]![frm2]![SearchAccount] and you'll want to change the code in your search button from me.RecordSource = "qryFilterPrice" to me.RecordSource = "qrySearchAccount"

Also be careful about using the # as part of the field name (ie. MR#). I would avoid using any non-alpha characters such as these (ie. !@#$%^*) in ANY field (or table names.) It would be much better to call this field something like MRNumber. (as a general rule, no spaces and no odd characters).

[jo15765]

OOOO, well the query wouldn't work if I was telling a [Form]! to point to a table, instead of the name of the form I was wanting it to go towards! And I completely overlooked my error in changing the VBA code. Thanks for the assistance, this is exactly how I needed it to work.

[pkstormy]

Cool! I'm glad you're able to get it sorted out. Please mark the thread as solved unless you need more assistance on this topic.