Desperate Help with Forms ASAP! Please

[davis1189]

have created a startup form for a table that contains
50 customer records. Of course within each record, there
are several fields that only pertain to an individual
customer. There are six different salespeople for these
customers.

I currently have my startup form set up with command
boxes that say, "open new order" and "review existing
order". If the salesperson clicks "open new order", they
currently come to a new order form (record). If the
salesperson clicks "review existing order", the first
order form (record) within the table opens whether it is
that salesperson's customer or not.

Is it possible for me to set up the startup form in a
manner in which when a salesperson clicks on "review
existing order", a box comes up and either has him select
his name from a drop down/list/combo box or key in his
name and only his records show up? If so, will it only
show the first record or will he be able to go to each
successive record simply by clicking "Enter"?

HELP! I know this is alot but this is my first database
I have ever built and I need still a novice with this.
Help!

 

[jeremie_ingram]

You could do this if you have some sort of field refrence to the sales person. There are a few options.

1) Add to your existing form a combo box from which they could select thier name.

2) Refrecnce the log in ID of the OS through VBA and compare the returned value vs a table of associated values (this will not allow anyone to see anyone elses records).

If you go with the 1st option, base the record source off of a query, and have the criteria refrence the selection they made in the combo box. This would work as a filter and return only those records with that sales persons ID. If they did not make a selection, then they could view all records. Or you could easily add code that would not allow them to progress until they made a choice.

 

[davis1189]

Thank you very much. I only wish I knew how to do this. I am really a novice and it sounds like this would work but can you please be a little more specific. I like your first option, however, I don't know how to create the query/filters.

 

[jeremie_ingram]

Ok, I will do my best to break this down.

First, create a query that retrieves all of the sales persons IDs from your table. Just create a basic select query, and use only the sales person name field from the table. Then click on the TOTALS selection on the toolbar (looks like a Greek E or a sideways M). The default is GROUP BY, leave it that way. This query will now grab all of the unique names from the table.

Go to the start up form. Here we have 2 existing buttons. The first one does what you want, so we dont mess with it.

Add a combo box to the form and set its data source to the newly created query. Give it an appropriate label. Set both the label and comboboxs enabled or visible properties to NO/False. Add another command button and select open form option. There should be a wizard prompt asking if you would like to filter the data based on the selection on the combo box. Since the second form will be based off of the table the combo box draws its data from, this works out great. Just select the field for the sales persons name on one side and the combo box on the other.
Set the visible/enabled properties to the second command button to false/no. In the on click event / event procedures for the original button, delete all but the heading and the end sub statement. Type Me. and you will see a list of all of the forms objects and properties appear. Select the items we added one at a time (Label, Combo Box, Command Button) and follow them with a . as well. Now it should give one more drop down list showing the properties of the item you selected. Reset the Enables/Visible so the user can now see/use the items. You don’t have to go through all of this trouble, but it makes it look more professional.
Another added step would be to close your first form within the code behind the command button that we created.

In case the wizard doesnt act like I said it would, but this code in the on click even / event procedures.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Form2" this is your second form
DoCmd.Close "Form1" This is your start up form

stLinkCriteria = "[SalesPer]=" & "'" & Me![Combo5] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click

I have created a small VERY BASIC example for you to look at. Let me know if you have any problems with it.

 

[davis1189]

Wow. I am so impressed and thankful to you for all of this assistance. No one on any forum has gone to such lengths to make sure I get something. I greatly appreciate it. I am getting ready to try it all and will let you know how it all comes out. I am sure with the attachments, I will at least be able to look at the design views and copy the code if I have to in order to get mine to work if all else fails.

Again, I will let you know what happens. I am getting ready to try it now.

 

[davis1189]

Hi Jeremie, I have tried this several times now, however, it is not working. Everything seems to be okay until I start typing in the code after I clear everything except the heading and end sub as you stated in your directions. When I type Me., the list of forms objects do not come up as you stated. Do you know why? Additionally, when I type in the code, it doesn't work either. Should I be typing the code on the event procedures of my original button, the command button or the combo box? I was a little confused on that part. Thank you again!

 

[davis1189]

Maybe it would help if I said it like this:

In my database, I want the salesperson to be able to locate his/her orders. Currently, I have created two command buttons: one says: “Add New Order” & one that says: “Review Existing Orders”. The “Add New Order” button causes a new record to open. The “Review Existing Order” button opens with the first existing order.


What I would like for the database to do is either have a button or drop down list on the Startup form which says: “Salesperson” which the salesperson could type or click their name from a drop down list and then click “Review Existing Orders” and their orders come up or they would click “Review Existing Orders” and another form comes up with the salesperson's name in a dropdown list and allows them to select their name and they are able to review their orders. Is either of these possible?

Thanks again!

 

[jeremie_ingram]

OK, to answer the questions....

The code goes behind the command button that I suggested adding to the form. Think of you form as it is, and when the user selects the second command button, this command button (third) appears as well as the new combo box and its label. The code in the open event of the form would hide these objects, and the second command button unhides them. I hope I didnt lose ya there.

The code behind the third button opens the next form, filtering the data based on the selection made in the combo box.

If none of this is working properly for you, try adding a combo box to the second form. The wizard should appear and ask what you would like this to display or do. You can select the third option to filter the records.

If none of this is either working or functional for you, you could copy you database, delete confidential info (and all but very little data to reduce size), compress it, zip it , and post it. I could look at what exactly is happening and what need to be done to get the filter that you want in place.

By chance did the example I posted work for you?

 

[davis1189]

Yes, your example worked beautifully. I only wish I knew code better because I could then understand how to copy and paste what you have and change the info as needed to make mine work.

I am going to delete confidential information and zip it so I can see if you can tell me what I'm doing wrong which is obviously a lot.

BTW, are you running 97 because I am running 2000 and I wonder if that is the reason it isn't working with the directions.

 

[jeremie_ingram]

Actually I am running XP developer edition.

I have helped quite a few in the past and since all version can open previous versions, I have learned my lesson on posting samples. I try to revert it to 97 so everyone can open it.

 

[sherry0618]

from another novice

I too consider myself a novice, having only created less than 10 databases.

Let me recommend a book to you that I have found to be a great help. It is called Running Microsoft Access by John Viescas and costs about $30 at Office Depot. Now, maybe more experienced users may laugh at using this, I have no idea. But for those who aren't "programmers" it perfect. I have learned so much from this book as well as the help file in Access. I just keep entering various keywords until I hit upon the one that gets me what I need. But I definitely recommend this book if you plan to continue using Access.

 

[davis1189]

Thanks Sherry. I may just have to do that.