I have recently acquired the job of updating our Access dB (2000 version) and I am a bit over my head here (my work with Access was limited to entering information into forms or running the reports requested - now they are asking me to update/fix/DESIGN these things!!). The database was designed by some other party many years ago, and has been copied to create new databases every year. This data base if for shipping of Christmas trees, so it hold load date, ship date, loading site, carrier, multiple customers (drops) and products. There are various reports that are run for the field crews so that they are always on top of what needs to be cut, what fields those will come from, etc. The problem I have is that this year they have added new product (which I know how to enter into the form that should be putting it in the table - which, in turn, should make it accessable by all reports, right?). The problem I am having are several:
In our Harvest/Product Summary Reports (I realize these names don't mean anything to you, but hopefully it will help in differentiating between the reports), I am able to see and link (in the properties window in design view) to the products that were existing, but I altered the name. However, the new products that I entered DO NOT show up. They show up in the form we use to enter the load information, but they do not show in the properties window - so I can't link these fields. When I look in the appropriate table, they products all exist as they should. However, in my xTab file ALL products that have ever been in any of the databases are listed. I can't change any of them because they are linked tables, so modifying them won't work.
In our Daily Carrier Load Sheet Report, Loads are pulled by Ship Date, show the load number, carrier, and is supposed to show what total product is supposed to be on that load. It is listed out by total product (i.e. if there are 8 drops and each has 2 pallets of D6P, it would show 16 D6P). This whole field is blank. All other information populates like it should. In design view, all that is shown is "Load Synopsis" in the field - but I can't seem to find WHERE the Load Sysnopsis is generated.
I am sure you will need examples, and I will work on getting those uploaded here ... but any thoughts or suggestions would be wonderful!
Thanks everyone!
11 1430
they have added new product (which I know how to enter into the form that should be putting it in the table - which, in turn, should make it accessable by all reports, right?).
If the database was designed correctly then YES it should!
I am getting my head around the rest of your post......more comments to come
You have several issues here, lets take then one at a time and you may discover the solution to the rest as we go.
On your Harvest/Product Summary report there are several things that you mention that needs clatification.
I am able to see and link (in the properties window in design view) to the products that were existing
I guess you mean design view of the report but the properties window of what?
but I altered the name
Flesh this out with more detail, I am just guessing as to what you changed the name of???
However, the new products that I entered DO NOT show up
Show up in what??? I am not sure exactly what they are supposed to be showing up in, I know on the report, but what in the report?
When explaining your issues you do need to be as concise and to the point as possible but you must remember, we can't see your database, reports, forms etc.
So, be concice but also be complete.
Just something that might help you.
If I was in your place this is how I might approach it. Start at the report.
What is it bound to? a query, a table, what? If the report is bound to a query
What tables is it querying? On the report
What controls are on it?
What are those controls bound to?
Map all of these things, and anything else that might be usefull, out on a piece of paper, it will help you get an understanding of how the database has been designed and give you a reference for tracing through why something isn't working.
Hello Delerna!
I had a wonderful reply typed out to you explaining what I had found about the report (I figured taking one at a time would be easiest) and how I had made screenshots for clarification ... first my computer froze while trying to upload the screenshot file (too big I guess). Which irritated me, but! I retyped the response to you, hoping I was as eloquent as the first round. Only to be timed out and losing all the reply again ... I think I need a drink ...
I wanted to let you know that I got your reply and am working on a reply (again) and will post it as soon as I have it. In the interim, is there another way to get you the screenshot file to you? Its too big to attach to this post.
Thanks again so much for your help and in advance for your patience!
You should be able to put the screen shots in a .zip compressed folder and then upload that.
In case you've never worked with a .zip before, just right-click on your desktop or in a folder, hover over 'New', and then select 'Compressed Folder' from the choices. Then drag your screen shot onto the folder and post it here.
Hope everyone had a wonderful weekend!
Lets try one report at a time. I will refer to the Product Sheet this round. I opened last years database to start fresh and added the new products in the Products Form – which should add the new products to the Product Table. Which it did. And the new products show up in the Loads Form (this is where all load information is entered – Load Date, Carrier, Customers and Products) as product available for each load. The Loads Form is where the information is pulled for the Product Sheet, Summary Report and Harvest Summary.
On our Product Sheet Report, I added 3 fields to the report – one text box to label what product the column is referring to; one field to show the product that appears on the load(s) (each row is a load and the product totals show up in the various corresponding columns); and then a field to sum all the numbers in each column. The problem begins here. When I right-click on the second field so that I can link it to the appropriate product (see screenshot of Properties Window), the product doesn’t show up as an available option. In researching where the information is pulled from, it is pulled from the xTab Query.
I opened the xTab Query to add the new products, and they are not showing as options there either. So, I guess I need to figure out how to get the xTab to update with the new products that have been added, but how do I do that? Once I figure that out, I can fix the others, with the exception of the Load Synopsis on the Daily Carrier Load Sheet, but we can tackle that after this is fixed.
Thanks for all of your help!
Hi Shannon
sorry for the delay, I have been away at a conferrence for the last couple of days.
There are no screen shots attached?
This statement
the product doesn’t show up as an available option
is a concern to me. I have the feeling that you might be a bit confused here.
In design mode, the product doesn't show up in the properties window, only the names of the product field will "show up".
For example
your product table might look like -
ProdID,ProdDescription
-
1 Drill
-
2 Sander
-
3 Press
-
4 Grinder
-
If you now create a report bound to that table and then edit the report and open the properties of a textbox on the report.
I get the feeling that you are expecting to see Drill,Sander,Press and Grinder as selectable options in its bindings property. This is not the case, you would only see ProdID and ProdDescription. The actual contents of those fields would ony be displayed when you run the report and never when you edit it.
Am I on the right track here? Is that what you were expecting?
PS
not that you should bind a report directly to a table
You seem to be on the right track with the XTab query.
If you can post the code (or a screen shot) for it it might help me to understand your descriptions better.
My instinct is telling me that if the query is selecting from the products table then you should not need to add anything to the query to get the new products to come in.
It should have been designed so that if you add a new product it automatically comes in.
Good morning!
No worries at all - I appreciate your time!
Here are the screenshots - hopefully that will clarify some things. And I can answer any additional questions that pop up.
I think it shows up on the screenshots (if you don't see this reference, let me know and I will get a screenshot for you), the reports we are running break down by individual product, so the product names do show up in the drop down in the Properties window. We have other reports - such as the xTab - where the option is simply the Product ID and Product Description, but that is not the case on this particular report.
Again, thanks so much for this help!
Looking at your screen shots
and comparing to your posts
will post back later
My first guess is that the join conditions in your "XTabDailyCarrierLoadSheet" query are filtering your new records out.
Its difficult to read it as the tables aren't placed will in the snapshot but it appears
"Products" is joined to "DropDetail" on the ProductID field
and
"DropTable" is joined to the "DropDetail" on the LoadNumber and DropNumberFields
You need to check and ensure that matches exist for them.
Perhaps you should PM me and we can arrange for me to get a copy of your DB
Sign in to post your reply or Sign up for a free account.
Similar topics
by: noah |
last post by:
Does PHP have a feature to associate Cookie sessions with a persistent
database connection that will allow a single transaction across
multiple HTTP requests?
Here is how I imagine my process: I...
|
by: Eric S. Johansson |
last post by:
I have an application where I need a very simple database, effectively a
very large dictionary. The very large dictionary must be accessed from
multiple processes simultaneously. I need to be...
|
by: halsa |
last post by:
hi
I need to append multiple reports to a single report.For
example I have 2 Pending Invoice reports to print.The
report viewer has to preview these two reports like two
different pages and...
|
by: Ken |
last post by:
Help!
I have a MS Access database made up of a single table. The data updates for
this database comes in the form of a single Excel spreadsheet, which is
imported into the database table.
I...
|
by: Jeff |
last post by:
We have multiple ASP.Net web apps in development. As a standard we
are looking to go with SQL Server to hold state information.
Can we have the multiple apps all point to a single State DB? Or...
|
by: paul.chae |
last post by:
I have a table in Access with about 3000 records. There are ~60 unique
values in the ID field for the 3000 records. What I would like to do
is automatically generate multiple Excel worksheets...
|
by: graham.dreyer |
last post by:
HI all,
I have a problem at the moment where i need to be able to create, edit
and delete records in multiple tables in a single access database, not
sure if it would be better to use SQL or...
|
by: Birky |
last post by:
Is there a way to have Access enter multiple entries to a database based on a form where the user has selected a certain criteria? I am hoping to have my form enter multiple entries within a table...
|
by: Marcin Kalicinski |
last post by:
How do I use multiple Python interpreters within the same process?
I know there's a function Py_NewInterpreter. However, how do I use functions
like Py_RunString etc. with it? They don't take any...
|
by: JoyceBabu |
last post by:
Can anyone plz tell me the advantages and disadvantages of using multiple databases / single database for all tables.
I have a site with more than a 100 tables. Current all the tables are in a...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |