query frm switchboard

[keden]

Hello all,

If I want the user to have the option to view all accounts or just the ones he/she is responsible, how would you suggest I do this. I have a form based off a query currently. I would like to use one button for the user to select "view all accounts" and another that they can chose to select "view my accounts". What I would like to happen is to have the query deliminated by the user nt id which is housed in a table by the accounts they are responsible for. I have the module set up to call out the users ID. How would I set the query to pull only records by their ID? would this bee on the on exit of of the switchboard? Or could I set it up that on load of the account info form, set the "user" (from the query) to delimit from the called NT id? If this makes absolutely no sense, let me know and I will explain more.


Thanks,
Keden

 

[keden]

would it be possible (better) to create a module that will set the deliminator on the query by current user name? Then set this off when the button is 'clicked' to access the current users records?

 

[keden]

Ok, I have gone a different direction. I replaced the switchboard with a self made form. I created an unbound field (hidden) that will be set as current username from the the on_click of a button with caption "view my accounts". I have also set code to requery on activate of my form with the information on it and set a deliminator in the query set to equal the value of the unbound field in the loaded form.

this works fine for the "view my accounts" What I thought would be fine and dandy is changing the value of the bound field when the "view all" button was clicked. The problem, when I try to pull all accounts, I get nothing returned.

any ideas to have it bring back all records? As you can tell, I have been bouncing around with where I am going with this.

keden

 

[WayneRyan]

Keden,

If you use a query, (and the Like operator), set the default
value to "*" and you will get all.

Wayne

 

[keden]

I tried to build an iif(null( [Forms]![Main_Form]![username]),"*",someform!username) expression into the query and set the someform!username as "" when the view all acounts button was pressed. I believe it is looking for a value of "*" in the data instead of reading it as select all. setting the deliminator of the query to = [Forms]![Main_Form]![username]) and had the button to view all set the value of the text box to "*" when it was pressed before it opened the form. I keep getting no fields returned. I also replased the "=" to "Like". .......same results. Would it be better to run an if,then,else statement in the "On Load" event and enter SQL query there? Though I know how to query using SQL, I am just beginning with VB and how the table works in relationship to pulling data from a SQL query and only returning the specified data.

Thanks for your help.

 

[WayneRyan]

Keden,

Can you compact, ZIP and post the DB here?

Wayne

 

[keden]

let me strip it down to the portions that I have in question and I will attach it. I may be in the am.

keden

 

[keden]

Here it is. If you enter your name into the tblCustomerAssignments table and push the button on the "main" form, you will see only the accounts you put you assigned to. The problem is with the "select all" button. I have omitted any attempts I have made to reduce confusion.

Thanks,

 

[WayneRyan]

Keden,

Your query had a reference to the control on the main form,
I changed it a little and made the selection control a combo.

With this method, you didn't need the criteria when you
launched the form.

Wayne

 

[keden]

so, when using the combo (never really used them before) I would still need to use the query as the recordsource for the form. How would I delimate the records for a particular record when there NT Login was selected and what would tell the query to select all if "select all" was selected in the combo? Do I need to code the sql in vb instead of using the access query builder and utilize if,then statements to run separate queries based on the users selection?

Keden

 

[WayneRyan]

keden,

This is getting a little complicated. Assume for the moment
that your form was based only on one table. I would
use the combo (no command button) and in the AfterUpdate
event of the combo, I would:

DoCmd.OpenForm "YourForm",,,"[Customer_Name] = '" & Me.cboName & "'"

If in the AfterUpdate, the cboName was blank, I would:

DoCMd.Openform "YourForm"

Your form was based on a query (because of multi-table).
It was joined on the column Customer_ID (not name).

The reason that I went with the combobox was that the
combo really has two columns; ID and Name. The query
checks column 1 of the combo for two coditions:

Customer ID = Column 1 of combo OR
Column 1 of combo is empty

Wayne

 

[WayneRyan]

keden,

I just realized that I didn't attach the db.

Wayne

 

[keden]

I was trying to get away from having the user choose their name from a list. That is why I was trying to retrieve by user login automatically. I was thinking of writing the SQL queries into the code using an if then else statement. If value is not null select deliminating by username, else select *. With my limited experience in coding, I was not sure how to tie the recordsource to the queries. Would I have to write that code in a module and use the module as the recordsource? Do you think this would work?

Keden

 

[keden]

Am I just not getting it? Is there another way I should be looking at this?

Keden

 

[keden]

ok, so I went with the list box to view accounts my employee. I then added a combo to give the user the option to select customers by name. Thanks for your help Wayne. I really apreciate it.


Keden