Solved

Help with creating a form to assign records to a database user and filter by that username on form open

Posted on 2011-03-02
12
292 Views
Last Modified: 2012-06-27
Hello, I have a database which several users here have been helping me with, and I would like help getting it to work.  

In the database there if a form frmAssign which I would like to modify [Imported].[Assignment] based on user selections.

The goal is to select a user from the list box (drawing from tblUser) and pass [tblUser].[Username] to [Imported].[Assignment].  I would like the user to either be able to select a range of years (from [Imported].[Year]) or reference numbers (from [Imported].[Call Number]) and assign the selected user from the list box when he or she clicks cmdAssign.

I would also like to clear the assignments for a user when they click cmdRemoveAssignment.

If anyone can help me figure out how to do any of the above, I would very much appreciate the feedback.

Thanks in advance,
bevo




 I have attached an example copy of this database to the question.
EE-assign-example.accdb
0
Comment
Question by:Bevos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 3

Expert Comment

by:DockieBoy
ID: 35024561
Hi there :)

I only use access 2003, but if you would like to convert your example to .mdb I would be happy to takea look.
0
 

Author Comment

by:Bevos
ID: 35036780
Hi Dockie, I've attached the .mdb file to this message.  Thanks!
EE-assign-example.mdb
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 35041006
You could try this,

In your table "Imported"  Click on your field "Assignment", then click on the Look up tab.  Set the Row Source to "tblUser", set the column count to 3, the column widths to "0;;0".

On Your form "frmAssign"  remove the unbound list box and add the "Assignment" control, then just select the user from the combo box.

As for removing the user from an assignment, I would probably be inclined to enter a user in your table "tblUser" as "None"  then you can assign no user to an assignment and still be able to search for assignments that have no user assigned.

This is the quick and dirty answer, personally I would be taking a very different approach that would require a great deal of work in restructioring your tables and changeing your forms.  happy to explaine further if you want, or you can try what I have suggested above.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Bevos
ID: 35057286
Hi dockie boy, thanks so much for the advice.  I made those changes and now I have a none option in the tblUser and have changed Imported.[Assignment] to work via list box.  I also made the necessary changes to the frmAssign to set the user selection to that combo box.  Now, I am curious about how I would do this record filtering for assignment.  
Would I need to do, for the range of years and call number, a SQL query to the effect of:
SELECT * FROM `imported' WHERE [Year] between 'me.txtyear1' and 'me.txtyear2'
similarly for [call number]
SELECT * FROM `imported' WHERE [Call Number] between 'me.txtCallNumber1' and 'me.txtCallNumber2'

I'm not sure about the next step however. How would I pass on the selection of the user from the list box, List34?

Thanks again for all of the help so far dockie boy
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 35162982
Hi there Bevos, sorry for the long delay, this one slipped through the traps.  Do you still require help with this?
0
 

Author Comment

by:Bevos
ID: 35165262
Yes, I haven't been able to figure out the next step yet, sorry.

Bevo S.
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 35169400
No problem, if you could please upload db with latest changes and we can go from threre.  :)
0
 

Author Comment

by:Bevos
ID: 35181195
Hi DockieBoy, here is the file we were talking about
EE-ABS-Example.accdb
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 35201240
Sory Bevos, need .mdb
0
 

Author Comment

by:Bevos
ID: 35225671
Hi Dockieboy, I don't think I can do this.  It says "You cannot save this database in an earlier version format, becase it uses features that requrie the furrent file format. Features that require the current file format include, attachments, multivalued fields, offline data, or links to external files that are not supported by earlier versions of Access."

Maybe I will close the question and reopen?

Thanks,
Bevo S.
0
 
LVL 3

Accepted Solution

by:
DockieBoy earned 500 total points
ID: 35228552
That will work.  Close this question and start a new one on the problem at hand :)
0
 

Author Closing Comment

by:Bevos
ID: 35229327
Needs more work on problem
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question