My department and limited the users to only specific tables based on their need and role. However, users have expressed the need to view past work. I do not want to give them access to the table - I am thinking that there should be a way to create a report that shows the data, but how would I set up a search filter option so they can find the one record that they are looking for?
View Repliesi orginally thought i should do this in excel but i think when the dataset becomes larger and i want to eventually get it on the web - i thought its access i need?
i have created a mini dataset as an exmaple of what im trying to explain..i have in first column the objects, and columns after this are the elements needed to make the object (i have just labelled the element titles a b c etc - there will be LOTS of elements once i get the dataset created). so if i had a red blue green cream elements - a search would bring back both lights and lamps that i am able to make.
object A B C D E F G H
lights red blue green cream
lamp red blue green cream
garage red blue green cream yellow brown purple pink
house orange white green blue red black
garden orange white green blue red
garden house orange white green blue red
is there a way of creating a search lookup to this kind of information? a form which you type the criteria in and it brings back what you can make? if i was one element away from an object could I use a wildcard function to say, if you had black you could make a house?? etc
I have a very simple Access database that contains a form where users enter sheduling information...I wish to limit a group of users to be able to read this information only and not modify or add to it.
The tables are in a back end mdb. The rest is in a front end mdb. And the users actually use the mde file when working with the schedule.
What in your opinion is the least complicated method for providing some users with the ability to add/modify and some with read only.
Thank you.
Suzanne
How can it be that from Design view I can go to data sheet view, but from form view there is no option for data sheet view? On form property sheet "allow data sheet view" is "yes".
View 1 Replies View RelatedI am displaying table records in access form Datasheet view. While business users accessing that form, they are hiding and rearranging some columns for their use. So it will be hard for other users to access. I just want to know any settings are avaible in MS Access or can we accomplish this using any events or macro or modules??
View 14 Replies View RelatedI support a database for some users who don't find the built in search function (ctl+f) to be useful enough.
The main data entry form of the database has fields for subdivision, lot number and address, any of which they may use to find the record they want. They're requesting that I add two types for searching:
* select sub and/or lot number as search criteria and have the selected record populate the screen
* type in a freeform address and have the record populate the screen
I'm debating about what the best approach would be to do this. Should I have a command button which opens a pop up form with the fields? If so, what is the code I will need to take the data selected/entered on the popup, run a query and then populate the underlying form? I think I need to pass parameters but I'm inexperienced at this so I need some guidance.
Also thinking that if I do create a popup form that I'll use it in the open event on the data entry form as well.
Thanks.
:confused:
creating a VBA that will allow me to search for a record in Table A, and once the record is found, allow me the option to add that record to Table B. Is this possible to do if Table A and Table B are not formatted the same (i.e. one has more fields than the other)? In summary, creating a VBA that will allow me to search in one table and once the correct record is found, allow me the ability to add it to another table.
View 7 Replies View RelatedI have a form in access where i need to select a record between two different dates. For example i have a "valid_from" and "valid_to" field. I have an unbound text box with short date format and calendar control inserted for users to select a date. This is named "drpdate".
I have a bunch of other filters also in the same form. Now my issue is that i have not been able to figure out how to put in a SQL statement which would give me the data which is between the "Valid_from" and "valid_to" fields based on the date selected in the unbound text box.
If the selected date does not meet the criteria, then it needs to be give the results from another table (which i have already done).
Selecting the "General" group as this involves SQL Server Stored Procedures (SP) and VBA code and Reports and and and...
Client has requested exception type reporting noting when a price in a Bill of Materials (BOM) changes.
I am thinking to solve this with the following steps:
1) EXEC SP to run "this week's" BOM reports, automated, figure out how to print to PDF or something
2) EXEC SP to run "this week vs last week" exception report. A giant nasty:
Code:
SELECT cols....
FROM [xyz]
LEFT JOIN [histxyz] ON [xyz].[partnumber] = [xyzhist].[partnumber]
WHERE [xyz].[cola] <> [histxyz].[cola]
OR [xyz].[colb] <> [histxyz].[colb]
OR etc...
through each of the fieleds that are hooked up to change tracking. Run that SP once, then use that temp table to generate customized reports based on parts per product which had a change.
3) Update weekly state snapshot of all parts remembering this week's state... transfer data from [xyz] to [xyzhist], so TRUNCATE then INSERT commands.
Seems slow and monotonous, the snapshotting "shell game" aspect... perhaps I may wrap that all into a transfer SP and allow the data to stay right on the server as it moves tables.
I have a form that I've created to allow users to do a ad-hoc query on data in a table. Most of the fields are setup as
Code:
Like [Forms]![Unusual Login Research]![CustomerName] & "*"
where I have a drop-down box with options for: >, <, >=, <= and between. I've tried a bunch of iterations of the same criteria and keep getting the error above. Just as a test, i was trying to at least get the between option to work with the following:
Code:
[Forms]![Unusual Login Research]![ScoreAtLoginCriteria]+" "+[Forms]![Unusual Login Research]![ScoreAtLogin1] And [Forms]![Unusual Login Research]![ScoreAtLogin2]
Where ScoreAtLoginCriteria is the drop-down, set to "between" and ScoreAtLogin1 and ScoreAtLogin2 are integers.
Lets say we have 100 documents. 30 of these documents contain the same exact set of instructions that i want to update/change. what options would i have that would allow for this 'mass update' so that i can change that specific instruciton and every document with that line would be updated to reflect it?
View 4 Replies View RelatedI am creating a a text box where the user enters a text then clicks an option from the option that is used as the criteria for the search e.g. Last Name, Phone , address then a command button wil run a query.
View 3 Replies View RelatedI have Server Inventory Database where everything is entered manually. I would like to add the ability to automatically update some of the data.
Ideally this would be done via querying WMI and inserting the values retrieved.
I would like to be able to have a button that when selected would query WMI of the server that is currently being viewed and update whichever records I define.
Hi
Just a quick question
Is there any way that I can view which users are logged onto the database?
Thanks
I am trying to limit what a user can see when accessing a database in access. I am only testing with this in access before taking it to SQL server. I have 3 databases and I combined the data into one set of tables and now what i am trying to do is a certain user can only view data from a particular database.
I have a table with the users id and password, as well as an ID number from each database that I am trying to tie to the users.
for example one user can view and add/edit data in databases 1 and 3 but not 2. while there might be a user that can view and add/edit on 1 and 2 but not 3.
I created a new field as a text box, converted it to Combo box, then the Values are pulled from a query and all that works fine.
I have a form that opens and displays these Fields in a Data Sheet view and the new Combo box doesn't drop down and is flagged as a text box in the property bar but no way to change it??
I need the drop down like the other Category field I have, that works but this one doesn't.
See screen shot.
You can see in the Category field, there is a Drop down, but in Category II there is not, even though this is a drop down field. - BUT in this data sheet view it doesn't show it as a drop down.
[URL] ......
I am currently updating our old Access DB 2003 to 2013 and wanted to make a few modifications along the way.I would really like to allow users to leave notes for each other on a front page. This front page form does not have any underlying table to it as it is only used as a switchboard.My question is, is it possible to have some sort of memo or text box that users can enter notes into that can be easily viewed from the front switchboard and then they can delete/update the notes as required?
View 5 Replies View RelatedI am having trouble with a datasheet its ran from a macro button using BrowseTo command.
I am having trouble with the where condition; I would like to show records where the [FittingDate] is blank...
I have tried isnull() and [FittingDate]=""
I'm new to Access. I started building a database for work (I work at a nursery) and I used John's Dynamic Search code for an Inventory Input Database but I was wondering is it possible to use this code to input data into a table.
So basically I'm creating a database where I can input Item ID, Description(Plant name), Date, Location, Yard, and notes. For the Item ID and Description that is where I used the dynamic search code (as a combo box) so I can easily enter the Item ID and it will search for the Description. I got all this to work except that when I choose my selection it does not save on to the records. The other fields does save just not the dynamic search.
I would like to make a search box and update my form with the person's data from the search box.
View 5 Replies View RelatedHi All,
Just wondered if anyone can help me with this.
I have a form based on a query that has 16 options for values in one of the fields. I wanted to put an option button for each value on a form that would allow the user to view the records based on which option buttons were selected and have the form update accordingly.
The way I've gone about it is to have some code in the after_update event thingimy of each option button that updates a boolean value in a table that's linked to the underlying query the form is based on - but this just seems a long way to go about this... plus it's not working...
I've searched the archives for clues on this but haven't come up with anything. So I just wondered, is there a better way than what I'm doing. I'm not after somebody to do this for me, just point me in the right direction.
Many thanks,
Peter
I'm trying to protect the frontend of my database from unwanted changes. How to disable the navigation pane, ribbon, and only display my navigation form. My navigation form has 10 buttons that open forms for data entry. I only want the users to be able to open forms and add data. I don't want them to be able to go to the navigation pane or anything other than the forms.
View 2 Replies View RelatedI have a report with 2 option groups, I cannot get both of them to filter the data simataneously, they will each inititally filter the data, but when the opposite group it clicked, the previous filter data does not remain filtered. I have posted the code for the two option groups, I know I'm doing something wrong.
First option group code:
Private Sub Frame43_Click()
Select Case Me.Frame43.VALUE
Case 1
Me.Filter = "allowable_weight Like '263000'"
Me.FilterOn = True
[Code] .....
I have a big problem with a db I am trying to do. It needs to hold info on the patients (fname,lname,phone,add&blood type). It needs to be able to do a search by the blood type and the lname. I did the sql coding. Although I am stuck on the lname search thing. The biggest problem is the design... This will be used by actual nurses, so it needs to be simpler than simple. Just a window that pops up and a field where they can stick the thing they wanna search and results. I am so very lost and bordering depressed... Pls someone help
View 1 Replies View RelatedHello All
Talk about frustrating and I am sure the answer will be easy for some.
I have a Main form with search criteria and a subform with the data. See the attached image for the layout. The data comes from a query.
I have a yes/no field called "CompletedP" that I want to use to search on. This field is to filter in/out completed jobs. The Frame is called the generic name "Frame60". I am using an option group with the choice of yes or no plus other search criteria using combo boxes. The combo box filtering is fine but the option box does not appear to form any part of the search. To search I select any of the boxes and select "Search".
I post the code here.
Any assistance would be very much appreciated.
Private Sub cmdSearch_Click()
On Error Resume Next
Dim strFilterSQL As String
Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE 1=1 "
'This code is for a specific search where you will need to enter the exact string
'The source for this code can either be from a table or query
If Me![Location] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub6.Location = """ & Location & """"
End If
If Me![Code] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub6.Code like """ & Code & "*"""
End If
If Me![ClientCode] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub6.ClientCode Like """ & ClientCode & "*"""
End If
If Me![ProjectCode] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub6.ProjectCode = """ & ProjectCode & """"
End If
If Me![StartDate] <> "" And EndDate <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSub6.DateAllocated between #" & Format(StartDate, "dd-mmm-yyyy") & "# and #" & Format(EndDate, "dd-mmm-yyyy") & "#"
End If
Select Case Me.Frame60.Value
Case 1
strFilterSQL = sSql & " Where [CompletedP] = -1;"
Case 2
strFilterSQL = sSql & " Where [CompletedP] = 0;"
Case Else
strFilterSQL = sSql & ";"
End Select
If Nz(DCount("*", "qrySearchCriteriaSub6", Right(sCriteria, Len(sCriteria) - 14)), 0) > 0 Then
sSql = "SELECT DISTINCT [JobID],[Location],[Premises Details],[ProjectCode],[Code],[ClientCode],[DateAllocated],[CompletedP],[FileNumber] from qrySearchCriteriaSub6 " & sCriteria
Forms![frmSearchCriteriaMain6]![frmSearchCriteriaSub6].Form.RecordSource = sSql
Forms![frmSearchCriteriaMain6]![frmSearchCriteriaSub6].Form.Requery
Else
MsgBox "The search failed find any records" & vbCr & vbCr & _
"that matches your search criteria?", vbOKOnly + vbQuestion, "Search Record"
End If
End Sub
"Dynamically search multiple fields" using my own customer data.
I'd like to add in additional searches as what he has will let you do one search but I'd like to, in his example, search on vintage and state, or the like. I assume Id need two multi-searches for this.
Right now the data is in excel so I have a completely blank page to fill.