Search Function (1 Viewer)

[agehoops]

oh yea whoops. Sorry i forgot to remove that bit. The mousehook thing is to stop the mouse wheel scrolling through records, and disables it instead.

Fortunately what you want it to is more simple than what i've got but the same principle. You just need a table for the permissions of each user type but only 3 permissions. Although i would've thought you'd only need 2 as write and modify are much the same, but this is your choice.

When a user logs in, it looks up the values in the table to see what permissions they have and stores that permission in a global variable. When a form loads, it checks the variable to see if they're read or write. If they are read only, then it locks every field on the form but you can still scroll through to see things just can't write. If the variable has write access, then none of the fields are locked.

If you send me the database you're working on with exactly what you want it to do, i could probably set it up for you if you want, to save you faffing around with VB cos it can get a bit complex in places?

Hope this helps
Aidy

 

[svtguy02]

COOOOOOOL...... You're the best!

Heres the current DB file with search functionality that works!

You can do that if you want. WRITE/MODIFY as one and READ as the other

Heres how id like the breakdown:

Usernames:

Admin - Write Permission
HSS - Write Permission
RSA - Read Only Permission (also ability to use search function)

 

[agehoops]

Ok gonna be a bit longer than was hoping. I just finished it, and was all working a treat and i just lost it all!!! Sorry!! I'm a tard. I'll do it again tomorrow and get it saved for certain! Should have it for you some time tomorrow, sorry for the delay

Aidy

 

[svtguy02]

ooh don't be sorry at all.... you're doing this out of your own good will and I am in debted to you! I REALLY REALLY appreciate what you're doing.

Whenever you get it to me, will be great Thank you once again!

 

[agehoops]

OK here we go! HOPEFULLY this will work for you. I've put in the login script for you, with the 3 different user types, i've also added a third option which is the search function, which can be enabled or disabled. There are a few things which you may need to alter slightly as i've not had much time to full test.

There is the login form which should now open first. MAKE SURE YOU LOGIN WITH THIS FIRST, OTHERWISE WHEN YOU OPEN OTHER FORMS IT'LL PROBABLY GIVE YOU AN ERROR MESSAGE, AS ALL FORMS OPENING RELY ON INFORMATION GATHERED FROM THE LOGIN SCRIPT!

You can put error handlers on each form to stop this happening if you want to but still...

There is a new User table and Permissions table, each explain what they're for in their name, pretty simple. There is also a form for each of these tables for them to be altered easily.

You may want to create buttons on the forms or a menu or whatever so it is easy to access these forms.

The passwords are test, test2 and test 3 for the admin, HSS and RSA accounts in that order.

Hope this helps, thanks

Aidy

View attachment Issues database.zip

 

[svtguy02]

You are truly a god-send! Thank you thank you so much.

I like the charts that you've made as well, going to be very handy for our usage

One thing I noticed is wen I opened the DB, it wants me to login. I close the login box without entering ANY credentials. I'm able to go RIGHT to the issues table and enter data, and it seems to accept it.

Probably THE biggest problem that renders the entire authentication portion useless is this:
1. Open the DB
2. Get presented with logon box
3. Close the logon box WITHOUT logging in
4. Browse to the USERS table
5. Change the admin password
6. Close the DB
7. Re-open the DB
8. Login as admin, with the new password and it lets you login

Anything I can do about that?

 

[agehoops]

No problem at all, hope this is the kind of thing you were looking for.

Would love to take credit for the charts but they were already part of the system, nothing i created. They're part of that microsoft template

The authentication login screen won't stop users altering data directly into the table, it will only lock the forms, which is fine, i just left this open as it is easier to develop the system without having these things hidden but can easily be resolved:

First thing is you need to tell access to hide the objects box (which lists all tables, forms etc) so that it is not visible to users. You can change this setting in the startup options of your database (Tools, startup options) you can fiddle with the settings in here to see what you want to change. You can still bypass this yourself by holding shift, but you don't tell the users this. (You can also disable the bypass by altering some of the files in the access installation folder. Google it to find out how)

This will stop users being able to access the tables so they can't edit the data at all unless they are in the forms, and the only way they will be able to access the forms is to login, which then restricts what they can do.

For the login screen, the password field, for it to display * instead of characters, go into the properties of the password box, and go the the Input Mask property, and set it to 'Password' (without the ' ') and it should blank it out for you.

The passwords can be accessed and changed through the Users form which you will need to create access to from other forms. This will probably need to be set as only visible by administrators for obvious security reasons but shouldn't be too difficult to do. The user permissions for each type of user can also be altered from the Permissions form which will also need to have a button made to access it.

Hope this makes sense

Thanks
Aidy

 

[Abrakadabri]

Search field - when double clicking search result - data entered to form, how?

I have a question/problem which I hope somebody is able to understand/know how to fix - regarding search field and how to get the data (search result) inserted into an active form (the form which the search field is present in).

I tried both the Company and search multiple files Database and think the search function looked very good - but what I want to do is the following:

I will search for a customer and when I have found the customer via the search function, when I double click the customer I want that customer to be filled into the form (the customer entered into the active form) - the field is called Customer.

Does anybody understand my question and know how to fix it?

Thanks in advance...

 

[agehoops]

Basically you need to use the on double click event of the results box, assuming it is a list box here.

On the even, tell it to open the main form, goto the control of the data you've found, (for example, if the search returns the ID number of the client or whoever it is then go to the ID field of the main form) and tell it to go to the record you double clicked.

DoCmd.GoToControl "ID"
DoCmd.FindRecord SearchID

That's an example of it

Aidy

 

[Abrakadabri]

Search field - enter data

Wow - what a quick response to my question.. Thank you so very much.

Think I understood what you meant - will try it and eventually come with a follow-up if that is possible...

 

[agehoops]

yea no problem, let me know how it goes

cheers
aidy

 

[Oldsoftboss]

Just to clarify...
Are you searching for a customer or (and what I think you might be wanting)
when creating a new record, have a search for customer button, and when the customer is found, insert to new record?

Both possible, and not too difficult.

 

[Abrakadabri]

A little too difficult too concentrate when those boobs in your avatar is "bouncing around"....

I think you understood what I tried to explain:

Want to search for a customer - and when that customer is found, enter the customer into the active record/into a new record.

But what I also have found out - and probably is even easier is the following:

If I just have all the customers in a combo box, when I type say "Mic" the combo box will go to the first record which has that value in the name (the combo box does that as default). And I think that will do it for me.... with some customizing...

If I type "Mic" I just want the records which has got that data in the name to be displayed (I want all the other records to be hidden).

And maybe one little more function;
Maybe just have a List box under the Combo box which updates and displays all the values that has got "Mic" in the name. So the user can just mark the value they want to enter.

Possible? And what will be the best/easiest way to do this?

In the first place; I think the combo box does it pretty well, but the function to hide the values that doesn't match what the user has typed will be hidden - could be a good thing - for a start. Is there an easy value to set somewhere to fix this issue?

Think my english wasn't the best here, but hope you understood. Please ask me to specify more if you didn't understand... Thanks in advance

 

[Triplesss187]

Help

i've been working on putting a search form like the search multiple fields example. basically i have the search working, but the part where you double click to view the record, i get an error... it says the microsoft jet database engine does not recognize "WMPrefix, WMSerialNumber" as a valid field name or expression....


basically i think the error is in the double click code part.....

Private Sub SearchList_DblClick(Cancel As Integer)
On Error GoTo Err_SearchList_DblClick
Dim db As DAO.Database
Dim rst As DAO.Recordset

DoCmd.OpenForm "GeneralArtifactInput"

Set rst = Forms!SearchForm.Recordset.Clone

rst.FindFirst "[WMPrefix, WMSerialNumber] = " & Me.SearchList
Forms!GeneralArtifactInput.Bookmark = rst.Bookmark

DoCmd.Close acForm, Me.Name

Exit_SearchList_DblClick:
Exit Sub

Err_SearchList_DblClick:
MsgBox Err.Description
Resume Exit_SearchList_DblClick

End Sub




Is this because i am using a composite key, and i entered the composite key wrong?

rst.FindFirst "[WMPrefix, WMSerialNumber] = " & Me.SearchList
Forms!GeneralArtifactInput.Bookmark = rst.Bookmark


basically this is where im getting the error message from.... any help would be appriciated!!!



Thanks!

 

[Oldsoftboss]

rst.FindFirst "[WMPrefix, WMSerialNumber] = " & Me.SearchList

The search field name should be the same as the listbox's bound column name.

So if you have column 1 as the bound column, the WMPrefix bit should have this name of that column.

Dave

 

[Triplesss187]

ok so i've been working on these search forms for hours...

Private Sub SearchList_DblClick(Cancel As Integer)
On Error GoTo Err_SearchList_DblClick
Dim db As DAO.Database
Dim rst As DAO.Recordset

DoCmd.OpenForm "GeneralArtifactInput"

Set rst = Forms!GeneralArtifactInput.Recordset.Clone

rst.FindFirst "[WMPrefix] = " & Me.SearchList
Forms!GeneralArtifactInput.Bookmark = rst.Bookmark

DoCmd.Close acForm, Me.Name

Exit_SearchList_DblClick:
Exit Sub

Err_SearchList_DblClick:
MsgBox Err.Description
Resume Exit_SearchList_DblClick

End Sub

Private Sub txtSearch_Change()
Dim vSearchString As String

vSearchString = Me.txtSearch.Text
Me.txtSearch2.Value = vSearchString
Me.SearchList.Requery

End Sub

Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click

DoCmd.Close acForm, Me.Name

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

on this search form an error message comes up that "the microsoft jet database engine does not recoginize 'SP' as a valid field name or expression"

honestly i dont know why it keeps doing it, i've changed the 'WMPrefix' to every category in my form and it just always give me the same message rst.FindFirst "[WMPrefix] = " & Me.SearchList

ok, Next

the file i am submitting is a sample.... basically i have a composite key, and when you search for a one section of the key the search works fine. as soon as i combine the criteria into one box, and then you search i get a runtime error.... take a look, i really apprciate this!

 

[Oldsoftboss]

The sample doesnt have the search list, or the tables to allow the combo boxes to work.

 

[Oldsoftboss]

Try this....

 

[cubsguy]

I've implemented a combination of the 2 searches offered here and everything works great! Thanks!

I was wondering if it's possible to blank out the search fields after a record is selected. For instance I type in a partial name, select the record from the matching possibilities, my form is filled in with my selected record, but the partial i had typed remians in the search field.

I used the search that is built right into my main form. I have it set up to offer 2 search fields, whatever I had typed remains and I'd like it to be blank. I fear that my user will look at the search they typed and wonder why it doesn't exactly match the record they selected, especially if they have used one search for one record then the other search field for another record, the 2 search fields will not be current but will display whatever had been typed last.

Sorry for the long read for probably an easy fix, hopefully it makes sense. thanks again guys for the search examples.

 

[agehoops]

Fortunately for you it is a very simple fix (or at least should be)

On the double click event of the list box that displays the results of the search, you have the command to open the main form?

After this, you simply need a line in there which will clear the search fields, so for example, if your search field is called txtCriteria then you'd need something like this:

form!formname.[txtCriteria] = ""

hopefully that'll work. May be forms instead of form but you can experiment a bit.