Can anyone see why my code is deleting parts of old records?

[aqueousdan]

I finally seem to be getting somewhere with my database. I am concentrating on my MBR section. I input date and time data and shift data into the MBR table.



I am also taking data from other tables and filling in combo boxes with that data which then goes into the MBR table.

I have followed examples of code to allow the combo boxes to take their information based on what is chosen in the combo box above it.

All seems to be working great except if I fill the form in and then close it. If I fill it in and then go back to a record before, it appears to wipe out some of the data. When I then go back to the last one that I was updating, the data for the last two combo boxes (engineer and machine) are missing.

I suspect it is to do with the code in the event procedure, such as

Me.Combo206.Requery
Me.Combo206 = Empty

etc...

Can anyone see what i've done wrong?

[davegri]

Code is hard to follow with non-descriptive control names.
Combo126 could be cboFactoryNameLookup, etc.

When I then go back to the last one that I was updating, the data for the last two combo boxes (engineer and machine) are missing.

I didn't see this happening.

[RuralGuy]

It might happen because the Combo Box is bound (has a control source).

[ssanfu]

As davegri said, you are not following a good naming convention.

The problem is that because you are not consistent in naming objects, you have switched fields in tables.

Tables "Engineers", "Factories" and "Machines" are using "SiteID" and "FactoryID" (number type fields),
while table "MBR" is using "EngineerName", "SiteName", "FactoryName" and "MachineName" (text type fields).

The row sources for the combo boxes in form "MBRForm" all have an ID field (SiteID, FactoryID, etc) as the bound field.
What do you expect will be displayed in the "MBRForm" for the Machine control, when you store a number (6) in a text field that is supposed to have a name ("Groover")??
(Answer is...... "Nothing")

You need to fix table "MBR" structure.


----------------------------------------
One other thing:

EVERY module should have

Option Compare Database
Option Explicit

as the first two lines. There is a setting that will cause any NEW modules to have "Option Explicit" added. In the IDE, click on TOOLS/OPTIONS, then click on "Require Variable Declaration".

[aqueousdan]

The combo boxes in my MBR form for the engineer and machine combo boxes were bound to MachineName and EngineerName but I couldn't get any further because I was shown an error message because the MBR table was expecting data for the MachineID and EngineerID fields. I didn't know how else to fix it.

[ssanfu]

In table "MBR":
Why are you using fields "SiteName", "FactoryName", "EngineerName" and "MachineName" (text type fields)??

Why aren't you using "SiteID", "FactoryID" "EngineerID" and "MachineID" (number type fields - Long Integers)???

[aqueousdan]

I'd never even looked at Microsoft access two weeks ago... I am getting confused. I thought I needed text fields due to putting text in such as engineer names.

Besides isn't my problem due to me trying to store numbers in text fields? I'm unsure how to change the MBR form to use the EngineerID and MachineID data to store the text from the combo boxes.

I presume once I do that I can remove EngineerName and MachineName, etc from the MBR table?

[orange]

aqueousdan,

Do you have a clear description of what you are trying to do? That is the business and related processes to be supported with your database. Can you share that with us?
I 'm seeing issues/problems in design and/or Access, but nothing to compare what you have with what you are trying to build.

[aqueousdan]

I work as an engineer for a company that makes a product over several sites. Each site has factories which have machines and engineers who maintain those machines.

At the moment, each time a breakdown occurs on a machine and an engineer fixes the problem we record it (the MBR) on a piece of paper and file it away. I am trying to create this with access.

I want to be able to add engineers and machines using forms as well as the MBR form itself. I want the combo boxes to cascade to make things easier for the people inputting data into this database.

[ssanfu]

I'd never even looked at Microsoft access two weeks ago...

Hmmmm.... Looks like you have been thrown in the deep end without your water wings.
You should really work through some tutorials to learn the basics.

Maybe it will help you if you compare your dB to mine....... I used descriptive object names and changed the table "MBR".
If you DON'T understand what I changed, you will have probably have problems later on.

[orange]

Dan,

I agree with Steve. Here are 3 videos to watch to help with concepts:
Intro to Database
The Relational Model
Data modeling

Spend 30-45 minutes working through one of these tutorials from RogersAccessLibrary. They show the Problem, the Process and include solutions. You can use this technique with any database.

Class Info
and/or
Consolidated Widgets

Good luck.

[aqueousdan]

I notice you have taken out the EngineerName, FactoryName, etc and there is now just the ID fields. I think you have then made a query which is pulling from the MBR table and youve then based the MBR Form on that. I cant however see why because there just seems to be the same data in the query as is in the table.

It looks like I was on the right track with the combo boxes for the MBR sheet to pull the information from the respective tables but the names of the combo boxes mean something now. I had done that originally, although not quite as well, but ended up deleting and putting more in a few times in frustration

I notice a menu which is fantastic and has code for it in the scary visual basic part of the program.

Could you please explain to me the add/edit Factories form? I see you have a combo box which shows the Site name and also the Factory name. Why is there a box for the Factory ID there that is invisible? and how are the combo boxes repeating down the form?

Thankyou so much for your help with this, although I was heading in a sort of good direction, I dont think I would have got to the correct solution by myself.

I have started to watch the information videos right now.

[ssanfu]

Could you please explain to me the add/edit Factories form? I see you have a combo box which shows the Site name and also the Factory name. Why is there a box for the Factory ID there that is invisible?

That is my "style" (such as it is ) In the past, I had trouble adding records in some forms, so I add and hide the PK field.
I use autonumber fields as PK fields and autonumber fields have no real world meaning.
Since autonumber fields have no real world meaning, autonumber PK fields should never be displayed, thus it is hidden.

and how are the combo boxes repeating down the form?

This is easy.. The form is set to Continuous Forms view. You could set the default view to Single Form, but then you would have to keep hitting the next button to see other records.