Most of my databases have something to do with logging who is doing what among other things. As such, I have a table for employees with all their information and link that information to the audit record using the primary key. My issue then is what is the best way to "delete" a user when they no longer work for the company? If I actually delete the employee record, then I end up with orphaned records in the audit table.
...
Seth Schrock
Last Activity: Mar 7 '22, 08:43 PM
Joined: Dec 30 '10
Location: Covington, IN, USA
-
Best way to delete a user
-
Sorry it has taken me so long to respond. I'm working on other issues at the same time.
@NeoPa - I rarely use custom right click menus, and it seems I may have found the most complicated method. I will try the way in the link and post the results.
@Isladogs - Currently, my backend is sql server with a lot of stored procedure calls, but I'll see if I can create a simple database with the same right click code to duplicate... -
Custom right click menu strange behavior
I've got a form with a custom right click menu, called via the Mouse_Up event on one of the textboxes. The strange behavior is that if I right click (getting my custom menu) and then just click out of it (click somewhere else to get rid of it), then my form loses its filter. If I click one of the items in the custom menu, then the filter stays in place. Is this a bug? Is there a way to program it to not lose the filter? The filter in this case... -
Originally, the Loan query had the field ValueLocation as just text in double quotes, not a field name in brackets. To fix it, I moved the plain text into the SQL view.Leave a comment:
-
Well, it turns out that NeoPa was correct. I moved the alias field from the access query to the SQL Server view and now the join works. Crazy. Not sure why that made a difference, but there we go.Leave a comment:
-
[Base].[ValueLocation] is a varchar data type. The other side is the alias. All the fields in the joins are either int or varchar.Leave a comment:
-
@Jim
Neither field is ever NULL.
@NeoPa
[Loan].[ValueLocation] is kind of a calculated field. It is defined as "Loan Value" AS ValueLocation in the [Loan] query. Do you think that is what is causing the strange behavior?
Also, both queries are based on ODBC linked tables. I'm not sure if that would change any characteristics .Leave a comment:
-
Left join on three fields giving strange results
I have two queries that I'm trying to left join together based on three fields. When I do, I'm getting the results of an INNER join (only the two records that match in both queries). If I remove one of the fields from the join (making the join on two fields), then I get the results that I'm looking for - all the records from the left table and the two records that match up.
Here is the query with the three fields left joined that...Last edited by NeoPa; Sep 28 '21, 01:21 AM. Reason: Great layout work but fixed TABs into spaces to make it work. -
During the process of creating a clean database to upload, I tried Rabbit's suggestion and it works beautifully! I'm uploading what I have to show how I did it.
***Caution - Very Complex***
If you open frmLoans, you will see my ultimate goal. Each record with a subform listing the appropriate checklist tasks. Some completed, others not. Now, the fun part.
Table structure
[tblLoans] is the...Leave a comment:
-
I don't have an example SQL of what I'm wanting as far as doing it through SQL. Right now I'm just in the planning stage and all I have is a scratchpad database to test ideas. The following does it via DLookup(), which isn't the method I want to use, but I just built this to provide a working solution with some SQL for you. [tblLoans] corresponds to [tblCustomers] from my simplified version.
...Code:SELECT tblLoans.LoanId_pk , tblLoans.CustomerName
Leave a comment:
-
@NeoPa
I was pretty sure that I couldn't directly use variable data in references, I was hoping that you or Rabbit knew some trick that resulted in a similar outcome. Both of you seem to be able to work magic and pull the rabbit out of the hat (pun intended ;) ) on the things you create.
Let me start with my desired outcome. I want the user to be able to see a list of people (tblCustomers) and then expand the checklist for...Leave a comment:
-
My actual process is very complex, so I tried to create a simple version to get to the heart of my question. However, it seems I left out some details in my quest for simplicity. I'm going to continue with my simple version, but try to provide some background. There are different types of customers, each having their own checklist. The tasks on the checklist vary in number and description. I also want it to be flexible, so that if a process...Leave a comment:
-
Query Performance Improvement
I've got a query that I'm using to track if certain tasks are completed. A task is completed based on different fields being populated. I have a table that lists which field to look for. My current solution is to use a public function to determine which field to look for and then check if there is a value in that field. The only issue is that using a function for a bunch of records decreases the performance. I'm hoping that someone knows some... -
Hmmm. That just might work. I have noticed that once I run the query, it doesn't ask me for a password for any of my queries for a long time. And it would be easy to build a "login" form that simply opens a connection using the provided credentials, allowing the queries to then run without the password prompt. I wonder if manually closing the connection would make the queries prompt for a password again. Definitely something to work...Leave a comment:
-
You are correct in your assumption. I too prefer AD, but in this case I'm connecting to a program that has its own user database.Leave a comment:
-
Well, I was hoping for a different way, but I'll probably just have to go that route.Leave a comment:
-
Pass Through Query with Custom Login Form
I've created a pass through query, but I don't want the credentials saved in the connection string. So when I run it, it asks for a password. Is there a way to pass it the credentials from a custom login screen instead of the ugly Microsoft screen that comes up? I guess I could change the connection string at run-time using code, but that seems clunky. I'm hoping for a query.execute, UID="", PWD="" type option, but I can't... -
Seth Schrock replied to Need to match data in two fields against data entered in the same fields and if theyin AccessI recommend reading SQL SUM() for this specific question and going through the whole SQL course from w3schools.com. You really need to have a good grasp on creating queries in SQL if you want to create your own database. Database Normalization is also a very important concept to understand so that your tables can be designed in such a way that your data is usable in all situations.
Unfortunately, that is about all the assistance...Leave a comment:
-
Seth Schrock replied to Need to match data in two fields against data entered in the same fields and if theyin AccessUse the Sum() function in a query, with the Payer and Check Number columns in the GROUP BY clause.Leave a comment:
-
If your number does actually does start with a 0, then your number is stored as text. This makes it easy to split up the different parts. The Left() function allows you to specify how many characters you want from the left side of the string. The Right() function allows you specify how many characters you want from the right side. For example, Right(LotNo, 1) will get you the shift. I'd put it in a query and then base your report off the query....Leave a comment:
No activity results to display
Leave a comment: