process to build connectivity between access database and vba forms?

[bunty]

HI All...
There is two think which I wanted to do.
1. I have design one form where I can able to add,delete and display all the records using navigation button.But I dont want to use Navigation button b'coz the size of record is more.SO, I wanted to build some search option so we are able to find the particular record which i want to update.

2. admin only able to see that forms where i can perform addition,deletion etc operation.I am able to do that but the problem is its showing admin record also.so,I dont want to display admin record into that forms.

Please tell me how to do that.

thanks

[geekgirlau]

It's a little difficult to work out what you are attempting to do ...

1. It sounds like you want to filter the form to find specific records, for example where you select a value from a combo box to show all records where a specific field matches that value. Is this correct? If so, start by searching on "filtering a form".
2. I have no idea what you're asking for here - can you be specific?
   • How are you currently identifying "admin"?
   • What do you want admin to be able to see?
   • What do you want non-admin to be able to see (or not to see)?

A sample database would be helpful if you can post one.

[bunty]

Hi Geek...
Thanks for ur response.see I have 3 forms called login (user or admin both can login),admin form(where user can perform some operation like add,delete,update or reset) and last one is search.
In login page there is two option button for user or admin.If admin will login it will redirect to admin page or If user will login then redirect to search page.That i have done.
1. even in the admin page I am able to add and delete user records.but the problem is at the time of deletion I need to move till that record which admin want to delete using navigation tool.its taking so much time.For that I want to create on search option where i can give the user id so that record will display and admin is able to delete it.
2.suppose admin is login its show his record in the form.so,I wanted to hide that.but if we are able to do the first problem the 2nd is autometicaly solve i fill.
For ur reference I have attached the the .mdb file.Please see that.I m waiting for ur response.


thanks

[bunty]

Hi Geek...
Thanks for ur response.see I have 3 forms called login (user or admin both can login),admin form(where user can perform some operation like add,delete,update or reset) and last one is search.
In login page there is two option button for user or admin.If admin will login it will redirect to admin page or If user will login then redirect to search page.That i have done.
1. even in the admin page I am able to add and delete user records.but the problem is at the time of deletion I need to move till that record which admin want to delete using navigation tool.its taking so much time.For that I want to create on search option where i can give the user id so that record will display and admin is able to delete it.
2.suppose admin is login its show his record in the form.so,I wanted to hide that.but if we are able to do the first problem the 2nd is autometicaly solve i fill.
For ur reference I have attached the the .mdb file.Please see that.I m waiting for ur response.


thanks

[geekgirlau]

Hi Bunty,

I don't have MS Access on site with me today, but there are lots of resources available on the web - if you search for "vba access filter form based on text box" you'll find lots of code samples to do this. If you search this Access forum for "filter" you will also find lots of examples.

Essentially you need the following:

• A text box to type the user name
• Add code to the "AfterUpdate" event for the text box
• Your code will be something like this:

[VBA]
Me.Filter = "[UserField] = " & Chr(34) & Me.MyTextBox & Chr(34)
Me.FilterOn = True
[/VBA]

[bunty]

hi ,
thanks dear...I have search a lot but I am not getting.I dont have any idea how to do that without navigation button.
THANKS

[geekgirlau]

My apologies for not getting back sooner. I haven't been able to get onto a copy of Access 2003, so I'll just describe what you need to do.

• Create a blank form, with no data source
• Add a text box to your form and give it a name (in my example I'm going to use "txtAdminSearch")
• Create a 2nd form bound to your table - set this to continuous, so you see multiple rows
• Close the 2nd form
• On the first form, add a subform using the wizard, and select the 2nd form
• On the text box ("txtAdminSearch") in the Properties add an AfterUpdate event
• The code for this should be as follows (you'll need to change the name of "UserField" as I don't know the field name):

[vba]Sub txtAdminSearch_AfterUpdate()
If nz(Me.txtAdminSearch, "") <> "" Then
Me.Filter = "[UserField] = " & Chr(34) & Me.txtAdminSearch & Chr(34)
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub
[/vba]

If you type something in the text box, it will filter the subform containing all your data to find the record(s) that match. If you delete the text in the text box, it will show all records.

I haven't had a chance to create this using your database, but the process above describes what you need to do.

[bunty]

Hi Geek,
Thanks for your help....now I am able to search a particular record based on user name.I need to write a code through I can able to add or delete record.Because what I did before its not working now.Tell me how to write a code for add and delete record.Here I am using only one form and there is no navigation button.only add,delete,reset and update button is there.
The search code which I have written is below:
[VBA]
Private Sub cmdSearch_Click()

Dim rs As DAO.Recordset, db As DAO.Database
Dim res As Boolean


'Set db = CurrentDb.Connect

OpenDatabase ("C:\StudyMart_masts1.mdb")

If Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."

Else
Set rs = CurrentDb.OpenRecordset("select * from user where user_Id='" & txtSearchString & "'")
If Not rs Then
Do Until rs.EOF
' MsgBox rs.Fields("user_Id")
' MsgBox rs.Fields("last_name")
' MsgBox rs.Fields("first_name")
' MsgBox rs.Fields("email_id")
' MsgBox rs.Fields("password")
' MsgBox rs.Fields("user_type")
' MsgBox rs.Fields("status")

User_Id.Value = rs.Fields("user_Id")
Last_Name.Value = rs.Fields("last_name")
First_Name.Value = rs.Fields("first_name")
Email_Id.Value = rs.Fields("email_id")
Password.Value = rs.Fields("password")
User_Type.Value = rs.Fields("user_type")
Status.Value = rs.Fields("status")

res = True
rs.MoveNext
Loop
rs.Close
End If

End If

If (res = True) Then
MsgBox "User id found"
Else
MsgBox "User id not found"
End If

'db.Close

End Sub

Private Sub Reset_Enter()
DoCmd.GoToRecord , , acNewRec

User_Id.Value = ""
Last_Name.Value = ""
First_Name.Value = ""
Email_Id.Value = ""
Password.Value = ""
User_Type.Value = ""
Status.Value = ""


End Sub
[/VBA]
Thanks

[geekgirlau]

You shouldn't need to write code for this.

If you have a form bound to either a table or a query, you can just use the navigation buttons to go to a new record, and start typing - no code required.

If your form is continuous, click on the grey record selector to the left of the record and press [Del] to delete the record - again, no code. If there is no record selector visible, you can delete the record via the Edit menu - again, no code required.