INSERT query wrong file path

[Lol999]

Not sure where to put this as it's not a query specific question I guess.

Anyway, when I run the query it keeps insisting it can't find the table in Documents, which is strange because the db is located on the Desktop and no copy exists in Documents!

Can't find any settings to alter so anyone have any ideas please?

 

[JHB]

The error message is telling you that the table name isn't in the database, and not what you thought it was.

 

[Lol999]

Nope, it is looking for the database in Documents, and it's on Desktop, and the table DEFINITELY exists.

 

[isladogs]

Nope, it is looking for the database in Documents, and it's on Desktop, and the table DEFINITELY exists.

Just a thought ....
Is the project name = Documents in the VB Editor?

If not, suggest you post :
a) the exact error message
b) the query SQL for us to look at for ideas

 

[Lol999]

There's nothing in the VBA editor, and here is the SQL:

INSERT INTO (Tbl_Find) Description
SELECT Description AS Description
FROM Tbl_Tools
WHERE Tbl_Tools.Part_No = Tbl_Find.Part_No;

 

[isladogs]

There's ALWAYS a project name in the VBE even if you've added no code
Open the VBE then go to Tools ... Properties to view the Project Name

My suspicion is that the project name is 'Documents'

Anyway your code makes no sense:

INSERT INTO (Tbl_Find) Description
SELECT Description AS Description 
FROM Tbl_Tools 
WHERE Tbl_Tools.Part_No = Tbl_Find.Part_No;

The first line is incorrect
The second line should state the source table name
The WHERE line should be replaced by a JOIN statement

The notation for your append query should be:

INSERT INTO Tbl_Find (Description)
SELECT tbl_Tools.Description
FROM Tbl_Tools INNER JOIN Tbl_Tools.Part_No = Tbl_Find.Part_No;

But that will still give you a key violation error as the Part_No value by definition already exists in the Tbl_Find table

You can append using an OUTER JOIN where the Part_No doesn't exist in the destination table

OR if it does already exist, use an update query to populate the Description field

UPDATE Tbl_Find INNER JOIN Tbl_Tools ON Tbl_Tools.Part_No = Tbl_Find.Part_No 
SET Tbl_Find.Description = [Tbl_Tools].[Description];

 

[Lol999]

There WAS a db in Documents, but it wasn't called "Documents" and it's now deleted. The query is now looking for a table called Tbl_Find in the correct folder, which exists, but Access says it doesn't :banghead:

I'm curious as to the problem with the SQL, apart from my erroneous placement of brackets.
The fact is, Tbl_Find is a stand alone table in the database and is used to hold data temporarily before being wiped clean.
The field Tbl_Find.Part_No is entered by the user via barcode scanner. My plan was to use the AfterUpdate event to run the query, identify the Part_No and select the corresponding field Description from Tbl_Tools and insert it into the field Description in Tbl_Find.
Hope that clears a few things up.

Thanks, Lol

 

[JHB]

The query is now looking for a table called Tbl_Find in the correct folder, which exists, but Access says it doesn't :banghead:

What do you call folder, the Navigation pane in MS-Access or what?
Post you database with some sample data, (zip it), + the name of the query in which you've the problem.

 

[Lol999]

I've attached a print screen of the error message I keep getting. I even created a new table called Tbl_Seek and altered the SQL accordingly but it insisted it couldn't find it!

 

[isladogs]

Its not looking for a table of that name. Its looking for an external database.

The form record source has 2'missing' fields

Unless you post the code or the db itself its going to be difficult to solve

 

[Lol999]

Attached is the offending database

 

[isladogs]

the offending database

You said it ....

There's lots of things wrong with it that you need to sort out yourself before I can do anything with this.
Hopefully you KNOW what its meant to mean whereas I can only guess what you are trying to do.

To start with ...

As I said in post #10 the form frm_Find has 2 undefined or missing fields: Location & Date_Booked.

As a result, I can't enter anything in those 2 fields - no error but nothing happens

EITHER make both of these unbound fields ... if you do this you should be able to save the record to the required table field(s) using After_Update events as you suggested earlier
OR change the form record source to include the table(s) holding these fields.
This will mean the data will be saved automatically after entry (assuming the form query isn't read only)
Location could be from tbl_Location or tbl_Tracking but I can't find a field Date_Booked anywhere.

There is a 'Date' field in tbl_Tracking - to avoid problems you need to rename this as e.g. 'TrackingDate' because 'Date' is a reserved field in Access.

I also had a quick look at frm_Booking & that's also got errors. I get error 3201 if I click Save button ...

I expect there are other issues but that's enough to be getting on with ...

Please work through & fix the structure of your forms to fix these errors.
Hopefully doing that will solve your issues but if not, re-post the db when you've solved the issues I've outlined

 

[Lol999]

Thanks for the help, it gives me somewhere to start.

Cheers, Lol

 

[Lol999]

Okay, Frm_Booking works exactly as I want it to so no problems there.

By trying to rapidly re-learn skills from years ago I have often been unable to see the wood for the trees so as to speak.

I've made all the text boxes on Frm_Find unbound, but when I try and run the AfterUpdate event it STILL keeps looking for the wrong database!

I've done a [CurrentProject].[Name] and it returns the correct name, Tool Tracker, so why it keeps insisting that the db name is Tbl_Find I have no idea, but the simple fact is I am stuck until it gets resolved because I need to run the INSERT query and it won't!

Losing the will to live.

 

[jdraw]

I suggest a couple of things:

Do you have a specification/requirements statement for what you are trying to do?
If so, please post a copy so we can get some idea of your environment and problem.

Post a copy of the database.

Review this tutorial from RogersAccessLibrary to refresh your understanding of table design/normalization etc.

 

[isladogs]

Okay, Frm_Booking works exactly as I want it to so no problems there..

You mean error 3201 is what you want!?

I've made all the text boxes on Frm_Find unbound, but when I try and run the AfterUpdate event it STILL keeps looking for the wrong database!

I've still got no idea why it does this but your SQL statements ALL contain errors.
For example, these three are from frm_Find

Private Sub Command9_Click()
Dim strSQL As String
strSQL = "INSERT INTO Tbl_Find (Location, Date_Booked) " _
           & "SELECT MAX(Location,Date) " _
           & "FROM Tbl_Tracking WHERE Part_No= '" & Forms!Frm_Find.Part_No & "'_"
CurrentDb.Execute strSQL, dbFailOnError

End Sub

Private Sub Part_No_AfterUpdate()
Dim strSQL As String
strSQL = "INSERT INTO Tbl_Seek.Description SELECT Description FROM Tbl_Tools WHERE Tbl_Tools.Part_No=Tbl_Find.Part_No"
CurrentDb.Execute strSQL, dbFailOnError

End Sub
'------------------------------------------------------------
' Command10_Click
'
'------------------------------------------------------------
Private Sub Command10_Click()
On Error GoTo Command10_Click_Err

    Dim strSQL As String
strSQL = "INSERT INTO Tbl_Find (Location, Date_Booked) " _
           & "SELECT MAX(Location,Date) " _
           & "FROM Tbl_Tracking WHERE Part_No= '" & Forms!Frm_Find.Part_No & "'" _

CurrentDb.Execute strSQL, dbFailOnError

For a start, SQL statements should ALWAYS end with a semicolon before the final quote marks - see my examples in post #6

Secondly lets take Part_No_AfterUpdate code (with an added

strSQL = "INSERT INTO Tbl_Seek.Description SELECT Description FROM Tbl_Tools WHERE Tbl_Tools.Part_No=Tbl_Find.Part_No;"

This can't work as there is no data in tbl_Find so there's nothing to append.

I suggest you create queries that do what you want.
Once these are working, you can paste the SQL into the VBE if you wish.

Also, tbl_Find isn't linked to any other table (Relationships window)
Should it be?

tbl_Tools has a subdataheet '+' symbol next to each record BUT you haven't set this up. Suggest you get rid of it

Losing the will to live.

So am I after looking at this.
As a result, I'm going to drop out from responding to this thread

Final comment - I suggest you scrap this & start again from scratch

 

[Lol999]

So am I after looking at this.
As a result, I'm going to drop out from responding to this thread

Final comment - I suggest you scrap this & start again from scratch

Okay, please see below for an explanation of what I am trying to achieve.

 

[Lol999]

What should have been a nice simple little database has turned into a nightmare for all concerned.
Firstly, please appreciate that when I post on here it is because I am at a loss as to what to do next. I have researched the problem and either cannot find the solution or need it explaining to me. As a result I might have tried many things and forgotten to clear up some of the code debris that doesn't work. Hands up, my bad.

The db is intended to track the location of tools, as in they are either in stores or somewhere else.
The purpose of Tbl_Display and Tbl_Find are simply to display data entered by the user before it is, for want of a better description, "batch processed".
Tbl_Display is in a relationship with Tbl_Tools simply so that the field Description, common to both, filters down.
Tbl_Find is to allow the user to enter, one at a time, a list of items that require recall. The idea is that an AfterUpdate event completes the data for Description, LAST Location and LAST Date. I haven't butchered the SQL for finding LAST values for Location and Date yet as I can't find an example of finding the LAST value, or MAX if that suits, for more than one field at a time.
The table can then be printed out or discarded and is wiped clean ready for the next use.
Frm_Booking DOES work. The user enters a location into the location box, then books all tools, one at a time which appear in the table displayed, Tbl_Display. Once happy the user clicks "Save Booking" and the data is written into the table Tbl_Tracking and Tbl_Display cleared of data ready for next use.
Interestingly the code behind the button "Save Booking" was provided by a member of this forum, so go figure.
I've attached a copy of the cleaned up db.
Many thanks, Lol

 

[jdraw]

Lol999,

Here's an old thread with a sample database related to a poster's request for an equipment movement management database.
It seems to me that you are designing as you go. That is, the requirement/spec is vague and you are having to fill in things. I could be way off base (that's happened before).

If the database at the link, and the associated dialog by posters don't help, then please tell us in simple English what you are trying to do. Start at the 30,000 ft overview and gradually add detail. It will help clarify your thoughts and will improve communications.

Good luck.

There are free data models that may help with design.
SEE this one.

 

[Lol999]

Okay, the spec is this:
The company want to be able to identify where tools are, either in stores or a job location. This is to enable them to recall as necessary for PAT testing.
The guys operating the system are not computer savvy to the extent of working with databases so I am trying to provide them with interfaces for entry of data, be it either for booking tools to a location, or inputting a list so they can locate where each tool is and recall it.

The data structure I believe to be sound, it is a dead ringer for the old video lending library where one member can have many videos and a video can be rented many times.

In this instance a tool can be booked many times, and a location can gave many tools booked to it.

Am I taking an iterative approach to system design? Possibly.

Hope this explains more that I am trying to achieve.

Thanks, Lol