Changing records on Main and Subform

[steve9495]

Hi all,

I'm a begginner in access so any explinations in as much detail as possible would greatly help!

I currently have an Pump table and an Pump serial number table.
In Pump Table I have the pump part types, in Pump serial number table I have the corresponding part numbers. I need this information in two tables as there is alot to manage.
For each record in Pump table there should be a matching record in Pump serial number table. Example for every new ESP there must me serial numbers attached with it - self explanatory.

Im trying to cycle through the records on the main form, and have the sub-form do the same. I currently have the main form able to add/edit records to the Pump table but have been struggling to find a solution to have the subform do the same with the Pump Serial number table.

I dont know if I should be using a query and if so what type. Or is there an easy way to just link the subform.



I also dont think I have the proper relationship built between the two tables.

Any help is greatly appreciated!

Thanks

[aselm01]

Can you supply a copy of the db so I can mess with it? I have an idea that might work, but I need to test it before I show you how to do it. I am a beginner too, but have been using access a lot lately and have learned a few things.

The copy does not have to be the whole db, but just enough to get it working.

[steve9495]

Thank you for your time.

[aselm01]

Thank you for your time.

I tried to open the db but when it is extracted and I open it in access 2007 it says the db is in an unrecognised format. Don't know what I can do with that.

[steve9495]

I dont know what to do. Everything appears to be saved as a standard Access database.

Here is a different one.

[aselm01]

I dont know what to do. Everything appears to be saved as a standard Access database.

Here is a different one.

Still not working. You are using Access 2010 right? I am using 2007 at work and maybe it can't read the file. Is there an option under "save as" to save the db in an older format? If not I will have to wait until I get home to work on it. I have Access 2010 at home.

[steve9495]

Yeah its on 2010. I did a quick google- looks like you cant view a 2010 db with 2007 due to a number of newer features. There is a procedure you can go through to remove all those features thus making is viewable in Access 2007, however that is a lot of work.
If you wouldnt mind checking it out at home- that would be greatly appreciated.

Cheers

[aselm01]

Yeah its on 2010. I did a quick google- looks like you cant view a 2010 db with 2007 due to a number of newer features. There is a procedure you can go through to remove all those features thus making is viewable in Access 2007, however that is a lot of work.
If you wouldnt mind checking it out at home- that would be greatly appreciated.

Cheers

Okay I thought so. I will check it out later then.

[aselm01]

Okay so what I did was create a new db that just has your tables in it, one query, and the ESP form (with a sub-form for serial information). I used the query (returns all records) as the source for the ESP form and used the serial table as the source for the serial sub-form.

I renamed the tables and forms to make them easier. I removed the duplicate fields from the ESP table and added a "PumpID" field in ESP. I then added the same field (PumpID) to the ESP Serial table. I made the "PumpID" field indexed with no duplicates in both tables and then created a relationship between the two "PumpID" fields with all three check boxes checked. This will ensure that when you add records to the form that the indexed unifying field (PumpID) is the same in both tables and is unique for each record. You will have to type in a Pump ID for each new record, but then as you type in your data it will update in the serial number sub-form and in both tables.

I tested it and as I added each new record the data was automatically saved to the tables. You can look at what I did in design view for each table and form to see it visually. I do not know anything about programing so I did not create any macros or VBA code.

I hope this helps and does what you wanted.

ESP_Test1.zip

[steve9495]

That is exactly what I was looking for, thank you very much!!

[aselm01]

That is exactly what I was looking for, thank you very much!!

Glad I could help.