Need help with Changes to a database

[Dave 14867]

Hello,

I found a database template that pretty much suits my needs and started to make some changes to make it more closely meet what I needed. In the process I have broke something and cannot for the life of me figure out what I have to do to fix it.

Right now the issue is if you open the "Parts Purchase Order" form and select the first record then select "Preview Order" on the bottom right, everything works great. If however you go to the 6th record and select "Preview Order", you get errors. I cannot figure out how to resolve this.

I did make some changes as to the Inventory Transactions table but I don't think that is the issue.

The error I get is "Syntax error in Join Operation".

While looking thru how the data was being gathered I did see a Join Statement but I cannot locate it again.

Can someone please help me figure out what is causing the error and how I can resolve it.

Thanks

Dave

 

[jdraw]

Dave,

For your own benefit and for readers, it would be a good idea to
a) save the original, work on a copy
b) record each change you make so you have a history of such changes
c) make a list of the things you need that the template doesn't do/support
d) create some test data and
e) identify what the test data should do when processed.
f) record all issues where what happened is NOT what was expected.

Good luck.

 

[sneuberg]

The join is in the record source of the Parts Purchase Order report. If you take that query and copy and paste it into the query designer SQL view, when you change to design view you get the same complaint about the join and the cursor is place in this part of the query.

ON Employees.EmployeeID=[Purchase Orders].EmployeeID) ON [Inventory Transactions].PurchaseOrderID=[Purchase Orders].PurchaseOrderID)

Note that Inventory Transaction is part of this rather complex query so I suspect it could be something you did to that table. I suggest returning the table to it's original state and see if that clears up the problem.

 

[Dave 14867]

jdraw,

I did make copies of the originals, The "Parts Purchase Order" report is a copy of the original "Purchase Order" report, The "Parts" table is a new table that is nearly identical in design as the original "Products" table, I believe the only thing that changed was maybe some of the field types may have changed but not sure, I exported the original "Products" table, replaced the few lines of data with my data and re-imported it as the "Parts" table. I then created the same relationships to the Parts table that exist in the Products table.

What I want to happen is that the Parts Purchase Order report functions the same as the Purchase Order Report, which it currently doesn't.

I also want the "Inventory Transactions" table to be modified in the same way which it currently isn't.

I could resolve this very easily by just appending the products table with my data but I have this hangup about the name products rather than parts, we use parts to make a product, this template was designed as a reseller of products purchased.

Thanks

Dave

 

[jdraw]

Dave,

we use parts to make a product

And do you sell parts and products?

I'm attaching the relationship diagram (adjusted the format for readability) from your database posted in #1.

What exactly did you add/change?
I don't use macros. I'd convert all macros to vba. Seems more readers/responders deal in vba more than macros, so you may get more detailed help (now or in future) with vba.

As with any database, you design your tables and relationships based on your business facts. Adapting a working application to meet your needs can be quite difficult. In my view you need to get the tables and relationships set up to meet your needs.

 

[Dave 14867]

jdraw,

I agree, but I liked the way it was all tied together and seemed to be a pretty good fit, using the template I mean.

I have gotten more things working and I guess I am going to get over my own issue with the "Products" table.

I will take a look at the you have done and use it to further my knowledge and gain from your inputs.

I was going to convert the macros but wasn't sure it was a good idea.

While trying to figure out what was happening in this template and trying to import my data into it and failing many times to get it right, I realized the power of Access, in that I mean, a field can be greatly manipulated such that what is shown isn't what is really there. If you look at the "Inventory Transactions" table, what is shown as the "Product ID" looks like a description of the product, however, that field is really a number field and it takes the ID of that item in the "Products" table and displays the description of that item as the "Product ID" and somehow changes it to text.

Thanks again for your assistance, and feel free to give me any other helpful advice to guide me on my journey. I am new at this and love doing it while finding it frustrating at the same time. I am sure with time I will get a better understanding of the underlying Code and Statements and there sure seems to be a limitless supply of that.

Thanks

Dave

 

[jdraw]

Dave,

Concerning the jpg I showed in post #5, this is just a reformat of the tables/relationships in your database. There are no extra fields, tables or relationships --the tables have been moved and expanded to show all fields.

 

[Dave 14867]

jdraw,

Yes I saw that, I had added the Parts Table and made the addition of the PartID to the inventory Transactions table.

I went back and started from scratch, imported my data and appended the existing tables and now things are working as they should. I still don't understand how the Data get displayed in the Inventory Transactions table as text when it is really a number, can you explain that to me, I believe it is being done by one of the "Select" commands in the Purchase Order Form, the Field I am referring to is the "Product ID" field in the "Inventory Transactions" table.

I have attached a copy of my latest file, can you look and see how it is being done and let me know.


Thanks

Dave

 

[jdraw]

imported my data and appended the existing tables and now things are working as they should.

Which is your data?

 

[Dave 14867]

The first few rows (20 in the Inventory Transactions table) are the originals, all my data has Mxxxxx as the Product ID, I also entered a test PO with the "PO Number" of "Test".

I just converted the macros also, can they now be deleted from the macro section or should I leave them there.

Thanks

Dave