Forms :: Restrict Column Records Based On Criteria
Jun 16, 2014
This seem to be simple for single criteria but i am not able to do it multiple. I want to restrict user to customer list they r able to see in form combo box drop down list.
users are in employee TBL
employee group criteria is in tbl_Groups which contains value Admin,Read Only, manager etc
employee r grouped through tbl_AssignUsersToGroups.
customer r grouped in customerType TBL. i.e class1, class2 etc
created EmployeesClassTypeRestriction TBL to store restrictions for employees
Restrictions criteria:
1-employee can view only specific class which includes all customers in that class
2- employee can view only specif customers in a class only.
3-employee can view only specif customers in a ANY class.
4- No criteria No restrictions and can view all customers in all classes( For Admin )
Tried to used Tempvar criteria in customer combo box qry but it works for above point 1 only.
Attached accdb sample also.
View Replies
ADVERTISEMENT
Mar 25, 2006
I am not sure if this possible, the methods I have looked up on the Internet take ages for the query to open but I'll give it a shot.
What I need to do is count (and increment) how many records are returned in a query for each person.
So if 1 person returns 4 unique records, in these records it shows 1,2,3,4 in each record.
The way i have tried is by using DLookup to check the next record and evaluate it using IIF but this takes about 25 seconds to return circa 50 records.
View 5 Replies
View Related
Nov 15, 2005
I have a database that I want to install on a client's pc as a demo only. I want to restrict the number of records he can enter to 3. Is this possible in access? I will appreciate any help. Thx!
View 2 Replies
View Related
Sep 26, 2005
Dear all,
I would like to seek your help on this problem.
I would like to do a search for my records based on 2 different criterias. If found, the function should just return a boolean value so that I can act further based on the boolean value.
The reason I'm doing this is that I have 2 similar tables that contains EventID and AttendeeID and these 2 tables cannot have the same EventID and AttendeeID. So before I can allow a record to be inserted in the 1st table, it must check that there is no record in the 2nd table that has the same EventID and AttendeID. If there is, then i cannot allow the record to be inserted in the 1st table.
The same thing applies when I want to insert a record in the 2nd table. It must now check the first table.
Recordset.find seems to be only catering to 1 criteria, not 2.
Pls help. Codes are most welcome too.
View 3 Replies
View Related
Aug 16, 2005
I have a table that stores records by "return date". I would like to create a delete query that removes all records that fall outside of the date range entered on a specific form (frmMainMenu).
I can deleted the records that match the form criteria, however that is not what I need. The criteria used to delete the records was:
Between [Forms]![frmMainMenu]![from date] And [Forms]![frmMainMenu]![to date]
Let's say that the frmMainMenu contain the date range of 8/1/05 to 8/10/05, however the table contained data from 6/1/05-8/14/05, I would like for the query to delete all records that have a date 6/1/05-07/31/05 and 08/11/05-08/14/05.
Any assistance would be greatly appreciated. Thanks in advance for your help![/SIZE]
View 1 Replies
View Related
Sep 20, 2005
Hello all,
I'm having some difficulty obtaining the answers I want from my query. I'd have been more comfortable using filters in excel to find the answers, but my dataset is too large in this instance.
I devised a query which I thought should achieve the results, but based on a subset analysed in Excel the answers are significantly different.
My table has about thirty fields, the query I've produced is this:
SELECT AH_orphans.Product, AH_orphans.Comments, AH_orphans.ESP, AH_orphans.[ESP Parts],
<snip, all other fields in table>
FROM AH_orphans
WHERE (((AH_orphans.ESP)="n") AND ((AH_orphans.FRU)="y") AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a")) OR (((AH_orphans.ESP) Is Null) AND ((AH_orphans.FRU) Is Null) AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a"));
What I'm hoping for is the subset which match the following conditions:
- ESP field is not "Y". Possible values Y, N, null
- FRU field is not "N". Possible values Y, N, null
- Only those records from EntitlementName which include the substring 'uptime'
- Only those records from Account which start with the subtring nation
- Only those records from Type which include the substring primar
- Only those records from [Is a parent?] which equal #N/A.
Asking the same questions in Excel I get around 1.5k records. If I use the quoted query on the same dataset in Access I get precisely 11.
Thanks for reading this far! If anyone can spot the flaw/s in my query, or indeed advise that I'm going at it completely the wrong way I'd be very grateful of some advice.
Cheers,
Alex
View 4 Replies
View Related
Jul 10, 2007
hi,
i am querying a single table that contains card numbers and corresponding transaction codes (amongst other things). a single card number can have multiple transaction codes.
i want to select all card numbers that have both 0100 and 0802 transaction codes.
if i use a select query with ="0100" Or "0802"as the criteria, it returns all card numbers that have either 0100 or 0802 transaction types. if i then change the query to crosstab and group by card number, it is clear that some cards have both transaction types.
when i try to change the criteria to ="0100" And "0802" it returns nothing at all.
i'm sure the solution is something really simple - any idea what i'm doing wrong?
View 7 Replies
View Related
Jun 3, 2013
I am trying to create a delete query that, for a given person, deletes records in Table B that do not have a corresponding record in Table A.
Here are the relevant tables:
tblStates holds StateID, StateName, and RegionID (RegionID is a FK to tblRegions).
tblPeopleStates is a junction table between tblPeople and tblStates.
It lists states assigned to people. It has 3 fields: PersonStateID, PersonID, StateID.
tblPeopleRegions is a junction table between tblPeople and tblRegions.
It lists regions assigned to people. It has 3 fields: PersonRegionID, PersonID, RegionID.
For a given PersonID, I need to delete records (i.e., states) in tblPeopleStates whose RegionID is *not* in tblPeopleRegions.
For example, pretend that tblStates shows that State IDs 1, 5, and 6 are all in Region ID (i.e., all have a RegionID = 10).
If Joe (PersonID = 200) has StateIDs 1, 5, and 6 in tblPeopleStates, but doesn't have a record for RegionID = 10 in tblPeopleRegions, I need to delete his three records in tblPeopleStates (i.e., the ones where StateID = 1, 5, and 6).
PersonID will be found on [Forms]![frmMain]![subform1].[Form]![subform2].Form]![PersonID]
View 8 Replies
View Related
Sep 21, 2015
I need developing a new delete query criteria to add onto existing delete duplicate queries for deleting duplicate values. I need the new delete duplicate query to take precedent over the other two so that those values retained in the first query are kept retained after going through the second and third delete queries. Attached is a document laying out the request.
View 1 Replies
View Related
Oct 29, 2006
Hello,
My "customer" form is based on sigle table. I have to restrict no of customers only 5 customers. can anybody help me ?
This is required for distributing a database.
Regards,
Satish
View 2 Replies
View Related
Oct 14, 2013
I want to build a demo db and make it so the user cant add more than 10 records. Perhaps a popup when trying to save the 11th...
View 4 Replies
View Related
Apr 8, 2014
I have a form with a subform. The subform is a continuous form with two controls, both textboxes. The value in the second box is looked up in a table, where the column name is the value from the first box.
I tried a lot already, this code seems to work but only for the first value of the continuous form and is not refreshed when I click next record on the parent form:
Code:
Private Sub Form_Load()
'get the value from the first textbox
strLabelName = Me.LabelName.Value
'get the ProductID needed for WHERE clause from parent form
strProductID = Me.Parent.ProductID
'use dlookup
strLabelValue = DLookup(strLabelName, "tblProducts", "[ProductID] = " & strProductID)
'place the returned value in the second textbox
Me.Text5.Value = strLabelValue
End Sub
Is it possible what I want and how may I establish this?
View 4 Replies
View Related
Jun 5, 2014
I have a report that I am trying to complete based on several queries. I am trying to count the number of records based on certain criteria and using the following DCount.
=DCount("[Calculated time]","IPG1","[Calculated Time] <= 0.04" And [Ship-to party] In ("SN00207PJZ","SN09162XXX","SN09324XXX"))
I want to count the number of IPG1 records that are under .04 and have the Ship-to party of the ones listed. I have tried everything that I can think of to get it to work but can's seem to get it to. I figure it's something easy but I don't see it.
View 4 Replies
View Related
Aug 28, 2013
I have a table with Zip CODE field. I uploaded my information to this table form an excel file. There were over 120K rows of data so no way i could do this in excel.
So I found that some of the ZIP codes are either blank or have less than 5 digits.
Can I build a a query with the ZIP code field that will return all zip code records with less than 5 characters or blank. I'd like to repair these zip codes and a update query will be my next step.
I repeat excel is not an option because when I try to sort by the Zip Code column my computer explodes (okay not literally) ....
View 3 Replies
View Related
Oct 12, 2005
I have a staff database in my office which holds, amongst other things a list of staff holiday taken. Presently the staff have no access to view holidays because they will also be able to view other employees details. What I need to do is enable members of staff to see their own holiday, but no others and Im really not sure how to go about it.
I have a workgroup in place; so each user has their own login/password.
Any ideas would be gratefully received.
Thanks,
Adam.
View 3 Replies
View Related
Aug 14, 2015
I want to result as dlookup function in access 2007, when I use dlookup like this
=DLookUp("GPFDed","[SalaryAll]","[EMPID]='" & ![Cmb_EMPID] & "' And [SalMonth]='" & "Jan" & "' And [Salyear]='" & [me]![Label17].[Caption] & "'")
it gives #Name?
What is the problem and how can i change the label caption to Combo box column 2 value...
View 1 Replies
View Related
Nov 20, 2014
I have a form, where one field is a combo box
The combo box is two columns linked to at table,
task and sla
You pick the task, and it displays the task
What i need is, another field, to equal the SLA part of that task.
View 5 Replies
View Related
Aug 24, 2015
I want to delete certain records based on the selected date. However, I come across with this is error - Run time error '3464' (Data type mismatch in criteria expression).This part is highlighted in yellow. I even used the debug.print to test out if the sql statement is executed properly.
Code:
DoCmd.RunSQL DelSummarySQL
Here is my full code
Code:
Private Sub cmd_Delete_Click()
Dim DelSummarySQL As String
Dim StartRange As Date
[code]...
View 2 Replies
View Related
Sep 29, 2013
How can I update (some columns) in a table from the same table based on a Criteria column in the same table.
View 2 Replies
View Related
Jul 16, 2012
How to restrict the updation of a record while entering until a save button is pressed on the form ???????? is it possible without the vba ?
View 10 Replies
View Related
Feb 23, 2005
Hi,
I have a form (say Form X) that opens based on the ProjectID criteria in the query, which is based on a record selected from Form1 [ProjectID].
I want to be able to open Form X using information from 2 forms - Form1 or Form2, both using ProjectID. What I've done so far works, but I get an Enter Value Parameter looking for the other Form. How do I write the statement so that it reads Form1 [ProjectID] and if that is not open (null??), then it goes to Form2 [ProjectID] and vice versa?
What I have is:
IIf(("IsNull[Forms]![FrmAddPrjInfo]![ProjectID]"),[Forms]![FrmPrjRvwerMgrDue]![ProjectID],0) Or IIf(("IsNull[Forms]![FrmPrjRvwerMgrDue]![ProjectID]"),[Forms]![FrmAddPrjInfo]![ProjectID],0)
Variations of this haven't worked. Perhaps IIF statement is the wrong way to go about this?
Any suggestions? Thanks!
View 5 Replies
View Related
Feb 12, 2006
Hello everyone,
I have a problem with opening forms.I have situation with 8 forms (small ones!) which must be open based on values in ComboBox1 and ComboBox2.
Can I do it with Select case (DoubleClick event), and how?
THANK YOU IN ADVANCE,
View 4 Replies
View Related
Mar 5, 2007
Hi all,
In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc
I have a column in an Access table listing various dates. I want the next column to be
populated with the next pay period end date after that date.
So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006
and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc
How do I do this?
Kind Regards,
Matthew
View 1 Replies
View Related
Mar 24, 2014
I have created a cross tab to extract pipeline and sales for Q1 2014, Q2 2014, Q3 2014 & Q4 2014... the user can select the quater from a multivalued text box...
Now for the final output, have created another query which pull the above four quarter in each column from the cross tab...now the problem arises when i change the quarter to Q2 2014, Q3 2014, Q4 2014 & Q1 2014..it gives an error "Microsoft office Access database does not recognizes "Query name" as a valid field name or expression".
The error is because the second layer of query does not identifies Q1 2014.
How do i make access change the column automatically when the Q1 changes to Q2...
View 1 Replies
View Related
Nov 30, 2014
I need to input a string into a column named "EventType". The code should first check if the column "Agent Name" contains any strings. If there is none, it will input "IBM Director" into the EventType column.
Once it has looped through the agent names, the code will then loop through the Details column and input into EventTypes based on what is displayed within the string.
These are the codes that I am using to achieve this, however nothing is being input into the EventType column.
Code:
Private Sub Command11_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Final")
[Code] ....
I think the problem lies with the code that checks the agent name. When I removed it, it managed to populate the EventType column based on the details. But I still need to find out how to check the agent name too.
View 4 Replies
View Related
Feb 7, 2005
Hi folks,
I have a form with a regular save record command button, however I also want to add in an action to open a second form based on information stored within the form.
So users may be directed to Form.alpha or Form.beta depending on their data.
Can any one help?
View 1 Replies
View Related
