New Database

[DaRTHY]

Hello Everyone,

I have several questions. I was not sure, which sub forum shall i use, that's why I'm writing in General.

I created new DB,(this DB is for our IT-Equipment) and also tables and relationships. I have also Forms. My main Form name is "Personal_Info". This Form has also 4 (5. will come) Sub-forms. All of them connected with Email. And every Personal has devices. Which personal if you want to look you can see their devices. I will also send my DB and there is some Dummy Variables.

What generally I need :

1- Search in Main Form: I checked from internet. There was interesting solutions. But I could not match in my head. Also write some codes but did not work. I deleted all of them. Only there was a simple search with Query. But i was not what i needed... Or may be it was, but I could not continue.

What do I need:

I have List-box. And DB-User can see all E-Mails as a list. On top of List-box there is search bar. In this search-box I wanna search E-Mails in List-box. And when I click on This E-mail, it should go directly that record then DB-User can see all devices (Sub forms), which E-mail searched.


2- New and Edit : When DB-User Click New Button, automatically opens form "New Devices" or for Edit Button opens "Edit Devices" (I did not created this form.)

What do I need:

In this New Form, there is some options. First Choose your device, then other questions. The important one I do not want to see all options when i choose one, then comes other text or combo box. Other boxes shall be invisible until choosing Device.
For example : First Question
Choose your Device : (There is combo box) DB-User chooses Laptop, Then other question will open. Choose your Vendor, then Category etc.

I created for every devices different Tables also for every Category or Status different. I really do not know how can I do that. Because after choosing one of them, Laptop then it shall save all information in laptop table.

I really need your help, how shall I start this ?

This is just the beginning.

I thank you for your helps and understanding.



EDIT : After warning of moke123 I compacted and repaired my DB.
Thanks.

 

[moke123]

Many of us will not download from outside links.
Suggest you do a comact and repair and put in a zip file.
If you need to, delete whatever is not relevant to your issue until it is uploadable.

 

[jdraw]

Darthy,

I don't think you need a table for each device type.
You may get some ideas from this free data model from Barry Williams site.
You can add/remove/modify the model to suit your needs.
Good luck.

 

[DaRTHY]

Thank You jdraw. I was thinking also same. I just created like that but I do not need those tables in Relationship:

Category.
Vendor.
Status.
(I will not delete. Because I'm looking them. But I can remove from relationship.)

But i had not thought every devices in one table. I will check them again. It was better to hear from prof. Person.
Thanks again

 

[jdraw]

I wasn't suggesting that you should delete whatever you have. I was just providing a data model that may be helpful in designing, testing and vetting the model before getting too deeply into physical Access database.
See this link for making use of a data model to refine your analysis and design.

 

[DaRTHY]

I understood, after check this DB-Example. It saves me from over tables. I'm updating now and send it again.
Thanks.

 

[DaRTHY]

Good afternoon everyone,

I updated my Database. (Uploading here again.) I have same problems. I want to choose easy way, but also I want to create more useful. I'm really bad for coding but I guess with VBA, I can get better results. I'm of course open better ideas.

1- I want to start search bar :
- Shall I use VBA or Query ? Query will be enough what do I really need? I wrote before, however I'm coping here too :

I have List-box. And DB-User can see all E-Mails as a list. On top of List-box there is search bar. In this search-box I wanna search E-Mails in List-box. And when I click on This E-mail, it should go directly that record then DB-User can see devices (in Sub form), which E-mail searched.

Thank you for your helps again.

PS: For edit & new buttons, after finish this search, I want to start.

 

[arnelgp]

try the "search" box.
use double-click on the list to
bring up the pertinent record.

 

[DaRTHY]

Thank you very much.

I can focus my other things on DB now. Hope so I can do them alone )

 

[DaRTHY]

Hello again Everyone,

I almost finished my DB ver1.0. You can find it in attachment.

But I need your help again. If you open my DB, automatically comes Form Employees. I created 2 different Forms. One of them "Employeelist"

It works so simple. If you want to add new Employee, you need to type empty text boxes and just click to add. After adding it is refreshing the Subform and you can see there. If you need to edit, on subform you are choosing and clicking edit button after your change, you need to click update button.
(ps: I have to add, if empty Email, name and surname then need error msg.)

Other Form is DeviceList. I copied that part from:
https://access-programmers.co.uk/forums/showthread.php?t=188663

Unfortunately Option Boxes are not working on me. I wrote same codes to show you. May be you can find this problem.

What do i need. Actually I need both. I mean, I need search part from "DeviceList" and Add,edit etc. functions from "EmployeeList"

Now my question is :
For example:
I decided to use DeviceList. I made a double click with "NewDevice" but When I change record source as a Table name "Device_Informations, I'm getting error. How can I fix it ? Because if that page can open, I want to delete, edit etc. to the record.
After this error, I tried to put those buttons and also text boxes in Form "deviceList" like "EmployeeList" but it was not working. Or i could not do.

Example 2:
I decided to use Form "EmployeeList", I need to put there This option boxes and same search bar on "DeviceList" (I know it is not working right now, because i want to show you to the problem)

hope so, I could explain myself. If you cant understand. Please say. I will try to explain more carefully.

Thank you for your helps or ideas.

 

[DaRTHY]

I also wanna ask how can i check 3 text boxes "cant be empty"

If IsNull("Me.txtMail" Or "Me.txtName" Or "Me.txtSurname") Then
        MsgBox "Mail Name or Surname can not be EMPTY!"
    Else

If i write onlx Me.txtMail ..... then works.

 

[theDBguy]

I also wanna ask how can i check 3 text boxes "cant be empty"

If IsNull("Me.txtMail" Or "Me.txtName" Or "Me.txtSurname") Then
        MsgBox "Mail Name or Surname can not be EMPTY!"
    Else

If i write onlx Me.txtMail ..... then works.

Try using three separate IsNull() functions. For example:

If IsNull(Me.txtMail) Or IsNull(Me.txtName) Or IsNull(Me.txtSurname) Then

Hope that helps...

 

[DaRTHY]

Thank you for your help. It works... but works only one time, if I click again, then code is passing by first IF and continue Else statement and adding empty box.

I guess somehow i have to add While Statement.

Private Sub btnAdd_Click()
    If IsNull(Me.txtMail) Then
        MsgBox "Mail can't be empty"
    Else
        If Me.txtMail.Tag & "" = "" Then
           CurrentDb.Execute "INSERT INTO tblEmployees([EMail], [Name], [Surname], [Title], [City], [Phone_Number]) VALUES ('" & Me.txtMail & "', '" & Me.txtName & "', '" & Me.txtSurname & "', '" & Me.txtTitle & "', '" & Me.txtCity & "', '" & Me.txtPhone & "')"
        Else
           CurrentDb.Execute "UPDATE tblEmployees SET [EMail] = '" & Me.txtMail & "', [Name] = '" & Me.txtName & "', [Surname] = '" & Me.txtSurname & "', [Title] =  '" & Me.txtTitle & "', [City] = '" & Me.txtCity & "', [Phone_Number] = '" & Me.txtPhone & "' WHERE [EMail] =  '" & Me.txtMail.Tag & "'"
        End If
    End If

    btnClear_Click
    
    SubEmployees.Form.Requery
    
End Sub

 

[DaRTHY]

And Also When i write this Criteria to my query :

Like "*" & [Forms]![frmDeneme]![Srching] & "*"

I'm getting this error : Enter Parameter Value Forms!frmDeneme!Srching

why is that happening ? This Srching is a Text-box.

 

[DaRTHY]

Hi Pat Hartman,

(I uploaded with new Relationships.)

Thank you for your response.

I'm using auto-number only in "Device_Informations". If it is not necessary, I deleted it.

Actually I created "Category, Status and Vendor" tables only for Information. But should I see in relationship. No I should not. I deleted in relationship.

Add an autonumber to the employee table and use it as the FK in the user table.

Why shall not use Email as an PK or FK? (I can add it, but did not understand why?) (only i did not change this)

For me actually 4 Tables are important. "Device_Informations" , "tblEmployees" and their connection "User" also I will add "Mobile Contract" and I will connect this Mobile Contract with Employee Telefon number.

 

[DaRTHY]

Hi again and thanks for your response
Normally you are right but we are small group and Emails are unique for us. That's why I can use them as an PK and FK. Also For device Information, we have our Numbers and as a PK I'm using them and Auto-number is not useful.

I just need to think simple. Actually I can do like this also. I can put one more field to Device Information Table and name is "Owner". And this "owner" field like User Table in a relationship with Email by Employee Table. I can also remove one more Table as "User"

Then could be Search more easy ?