New Database (1 Viewer)

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.

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.

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.

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

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.

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

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.

Click to expand...

Thank you for your helps again.

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

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

Thank you very much.

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

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.

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



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

DaRTHY said:

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

Code:Copy to clipboard

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.

Click to expand...

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

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.

And Also When i write this Criteria to my query :


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

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

All of them connected with Email

Click to expand...

It is a poor design choice to use a field which can be changed as the PK and FK's. Add an autonumber to the employee table and use it as the FK in the user table. Also I see that all your other tables have autonumbers but you are not using them. NEVER have an autonumber in the table if it is NOT the primary key. There is no point and there is a potential for error. That makes all your other joins incorrect. For example, the PK of Vendor should be VendorID and it should join to VendorID in the DeviceInformation table. Vendor_Name is ONLY stored in the Vendor table. You are joining the two tables on data field to data field which prevents you from enforcing RI. Proper relationships are data field to PK. All the tables need to get cleaned up before you move on. It's a fair amount of work - it would take me an hour to clean it up, it will probably take you longer. I would also turn off name auto correct - at least while you are doing this, permanently is even better. name auto correct will not help with this process and it can cause strange errors.

Once you get the tables cleaned up and the forms working again, we can get back to your search question.

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.

Click to expand...

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.

The reason is that email addresses can change. I'm about to change mine. It's a pain but it is necessary. When you have related tables and you change the PK, all related FK's must also change. Although you can do that if you properly implement RI, it is still best to just keep data as data when possible.

Simply removing tables from the relationship window doesn't eliminate the need to enforce RI. If the tables exist and data in one table has to match data in the other, you need a relationship and it is important to enforce RI to make sure that the relationship is enforced and that you don't create orphaned data. You currently have orphaned data in the User table. RI can also not be enforced on the Status table.

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 ?

I'm not going to argue with you. You have convinced yourself that your situation is somehow "special" and so proper technique isn't relevant in your case.

I looked at your database. It has relationship issues on top of the misuse of email as a PK. Everything you have to do becomes more complicated when you are working on a database without a solid foundation.

I just need to think simple

Click to expand...

Correct is not complicated. It is often easier than "simple". Once you understand basic design principles, it frees you for other things because basics take no thought at all.

You're also using unbound forms for data entry. Why? That isn't simpler than using bound forms if simple is what your goal is.

Most of the forms I tried don't work and I'm not inclined to fiddle with them with the schema not solid.

You are not helping yourself by jumping into making forms and writing code before the table schema is sound.

RI is your friend, not your enemy. It doesn't make things harder, it makes them easier since it puts some of the burden of data validation on the database engine rather than you having to write code to ensure validity. It is actually critical if you insist on using changeable data as a PK. I know you think that emails will NEVER change. Well, I've had corporate email addresses that were changed so "NEVER" isn't reliable. Of course if valid data isn't a goal, then that's what you'll end up with - invalid data.