INSERT INTO locking table?

[kjworm]

Greetings all,

I have a popup form that is mostly populated with information from a table based off a user's selection from the form the popup is initiated from. They select a record from a dropdown, which pulls the information displayed in the popup form.

In this popup form, the user then enters a quantity number and a few other details. When they click the "Add" button to run the sql statement and insert the details into a new table, I get an error that looks like
????? ' TableName ' ????????

I am using Access '97 on XP.

Here is my code for the "Add" on-click event:

Dim ShortPN As String
Dim DueDteConv As Date
 
Dim mySQL As String
 
    If [OverrideDefaultNum] = "Yes" Then
    ShortPN = [OverrideNum]
    Else
    ShortPN = [DefaultPartNum]
    End If
 
    DueDteConv = Format([DueDte], "m/d/yyyy")
 
    mySQL = "INSERT INTO Queue 
        (ShortPN, Qty, Setup, Machine1, Machine2, EntryDte, DueDte) " _
    & "VALUES ('" & ShortPN & "'," & [Qty] & "," & [Setup] & ",'" & [Priority1]& "','" & [Priority2] & "',#" & Date & "#,#" & DueDteConv & "#);"
 
    DoCmd.RunSQL mySQL
    DoCmd.Close
 

The sql statement works if I run it without the form open using the output of my debug.print. After I get this error, if I open the "Queue" table, it appears to be locked as in no records can be inserted in table view. I have searched the forums and have been unable to find anything similar. Any information would be appreciated!

[FishVal]

Greetings all,

I have a popup form that is mostly populated with information from a table based off a user's selection from the form the popup is initiated from. They select a record from a dropdown, which pulls the information displayed in the popup form.

In this popup form, the user then enters a quantity number and a few other details. When they click the "Add" button to run the sql statement and insert the details into a new table, I get an error that looks like
????? ' TableName ' ????????

I am using Access '97 on XP.

Here is my code for the "Add" on-click event:

Dim ShortPN As String
Dim DueDteConv As Date
 
Dim mySQL As String
 
    If [OverrideDefaultNum] = "Yes" Then
    ShortPN = [OverrideNum]
    Else
    ShortPN = [DefaultPartNum]
    End If
 
    DueDteConv = Format([DueDte], "m/d/yyyy")
 
    mySQL = "INSERT INTO Queue 
        (ShortPN, Qty, Setup, Machine1, Machine2, EntryDte, DueDte) " _
    & "VALUES ('" & ShortPN & "'," & [Qty] & "," & [Setup] & ",'" & [Priority1]& "','" & [Priority2] & "',#" & Date & "#,#" & DueDteConv & "#);"
 
    DoCmd.RunSQL mySQL
    DoCmd.Close
 

The sql statement works if I run it without the form open using the output of my debug.print. After I get this error, if I open the "Queue" table, it appears to be locked as in no records can be inserted in table view. I have searched the forums and have been unable to find anything similar. Any information would be appreciated!

Hi, there.

• Check RecordLocks property of the form
• Check the Date output goes to your string in a proper m/d/y format.
  Copypasting the string into query designer is not the same as soon as smart query designer silently tries to adjust date value to fit m/d/y format.

Regards,
Fish

[kjworm]

Hi, there.

• Check RecordLocks property of the form
• Check the Date output goes to your string in a proper m/d/y format.
  Copypasting the string into query designer is not the same as soon as smart query designer silently tries to adjust date value to fit m/d/y format.

Regards,
Fish

Thanks for the response.

I checked the RecordLocks and they are set to No Locks.

I have tried inputting the date into my string textbox in various formats (i.e. 12-4-2007 and 12/4/07) and a MsgBox of my DueDteConv returns 12/4/2007. I'm not sure what else I can do to see the date format in the smart query designer...

As a side note, the reason I'm changing a string to a date is so that I can control the format consistently for all of the dates I'm using...

Any other thoughts? I intially had an autonumber field as the PK, but thought that may be a problem so I removed it. Would it be better to put it back in?

[FishVal]

It is rather weird.

I guess both forms: popup and that opening popup are unbound. Am I right?

[kjworm]

It is rather weird.

I guess both forms: popup and that opening popup are unbound. Am I right?

The form that the popup is opened from is unbound. On this form, however, there is a subform that is bound to a query.

The popup form is bound to my master table, except for the user input fields which are unbound. My insert statement is taking some of the info from the popup that is pulled from the master table along with the user input and inserting this information into a different table.

[MMcCarthy]

There are a couple of things I would suggest ...

• Check the spelling including upper case lettering of the table name
• Check the spelling of all field names
• Check the datatype of all field values are accounted for in the query
• Are any field from this table not included in the update, if so then check that none of those fields are set to required.
• Add the Me! identifier before all control names and check that all control name properties are correct. Remember that it doesn't matter if the control source of the textbox is ShortPN, that doesn't mean the the textbox is called ShortPN.
• Double check the output of Debug.Print and make sure all the values are as expected.
• Try copying the text of the SQL statement from the immediate window after the debug.print and paste it into a query. Save the query and see if that works or gives any errors.
• Try renaming the table to tblQueue and see if that helps.
• Lastly, try recreating the table and importing the data from the original table after you create the new one. It's possible you have a corruption problem with this table.

[kjworm]

There are a couple of things I would suggest ...

• Check the spelling including upper case lettering of the table name
• Check the spelling of all field names
• Check the datatype of all field values are accounted for in the query
• Are any field from this table not included in the update, if so then check that none of those fields are set to required.
• Add the Me! identifier before all control names and check that all control name properties are correct. Remember that it doesn't matter if the control source of the textbox is ShortPN, that doesn't mean the the textbox is called ShortPN.
• Double check the output of Debug.Print and make sure all the values are as expected.
• Try copying the text of the SQL statement from the immediate window after the debug.print and paste it into a query. Save the query and see if that works or gives any errors.
• Try renaming the table to tblQueue and see if that helps.
• Lastly, try recreating the table and importing the data from the original table after you create the new one. It's possible you have a corruption problem with this table.

Thanks for the help. I figured out why the table was locked while checking your suggestions:

On my main form that the popup is initiated from there is a subform based on a query. This query is pulling data from my tblMasterList and my tblQueue. The popup button used to execute my INSERT INTO statement attempts to insert data into my tblQueue that is already open by the subform. My fix was to close the subform before opening the popup and then reopening the subform after running the SQL. This seems to work fine. My goal was to be able to add data via the popup and then have that data immediately displayed in my subform. If there is a better way to do this, please let me know!

[MMcCarthy]

Thanks for the help. I figured out why the table was locked while checking your suggestions:

On my main form that the popup is initiated from there is a subform based on a query. This query is pulling data from my tblMasterList and my tblQueue. The popup button used to execute my INSERT INTO statement attempts to insert data into my tblQueue that is already open by the subform. My fix was to close the subform before opening the popup and then reopening the subform after running the SQL. This seems to work fine. My goal was to be able to add data via the popup and then have that data immediately displayed in my subform. If there is a better way to do this, please let me know!

you would normally be able to add data to tblQueue. Your problem is that your form is locked to read only. This must be locking the table.

You could add some code to set the allow additions property of your form to True before opening the popup and to false when the popup is closed. If you do this though I would suggest you make the popup modal