Two Query Problems

[Freakinchair]

Alright,

Problem #1 -

Im attempting to append two values into a table when a button is pressed. There are two cases for this, for my first case it works fine. The second case involves choosing a pre-existing record to append into a reference table. Here is the code:

DoCmd.RunSQL "INSERT INTO XREF_FILE_INSPECTOR " _
& "(FILE_NUMBER_CD, INSPECTOR_NUMBER_CD) VALUES " _
& "([Forms]![GeneralFile].[txtGeneralFileNumber], cmdInspector.Column(0) )"

The problem is that "cmdInspector.Column(0)" isnt actually getting that value and I get a runtime error that declares it is not a function. How do I get the proper value?

Problem #2 -

I've also create a button to remove a selected record from a list. In order to do this I just created a "Delete Query" so that when a button was clicked the query would delete the selected record from the correct table. Here is the code:

DoCmd.OpenQuery ("QryRemInspector")

I run the button and it asks whether I want to delete the selected record (I click yes) and then I get an error:

Error 3125: QryRemInspector is not a valid name. (stuff about too long, punctation etc...) I know this name is valid and it is the name of the query I need to use. What could be going on?

Also - I am using Access 2000 if that makes any difference.

Thanks for any help guys!

 

[Dennisk]

1). You are not substituting the values - this is how it is done

"(" & [Forms]![GeneralFile].[txtGeneralFileNumber] & "," & cmdInspector.Column(0) & ")"

2) ? try running the query from the database window

 

[Freakinchair]

Ah, your reccomendation for problem 1 worked! thanks

As for problem 2, even if I open the query by itself from the database window it gives me the same error. I renamed it a few times, all giving the same error too. I then made another delete query and tried it and it gave me the same error.

This is so weird, is there a naming convention for queries of the delete type?

 

[Rabbie]

Please open your query QryRemInspector in SQL view and post the SQL here then we maybe can see what is the the underlying problem is.

There is no hard rule as to what a query can be called. Just reccommendations!

 

[Freakinchair]

Ah, I figured out what was wrong, the error was very misleading. I forgot to put the quotations in the criteria field.

I have another problem though that I've been working on, I have a list box where the user selects a record and clicks on a button which deletes a reference in a table.

One of the pieces of information that needs to be deleted is displayed in a text box and the other is in the first column of the list box. I made a delete query to take care of this and I can reference the text box fine but Im having trouble referencing the chosen value in the list box as it keeps returning 0 rows to be deleted (when I know it exists).

Whats the proper syntax for referencing a chosen record in a listbox and the first column? I've tried
"[Me.lbThisListBox.Column(0)]"
but it doesnt work (nor does alot of other variations that i've tried)