help needed, brain gone! (1 Viewer)

[dtdukes86]

hello all , its been a little while so here am I again wanting to pick someones brains as this issue has stumped me.

i have an order form , with subform , the subform is "sold items" and the 2 forms are linked on OrderId and OrderFk

nice and simple ,.

Here's the snag

when the user makes a new order they can add as many products into the sub form as they like this is later added up and invoiced no issue, they can also delete them using a little delete button that runs a del qry and removes the line from the subform datasource and requrys the subform .. works fine.

the issue is !! once the user has deleted any row they cannot add a new row until the order form is closed and reoped.

when they try to add a new row after a delete button has been pressed the primary key doesn't populate and therefore it throws up a load of errors when trying to save the new record. I've tried a hundred and one way to get it to populate using all kinds of vba save commands but it doesn't save with no id so I'm stuck why doesn't it populate when it works fine if I don't delete any records.

if its relevant the setup is front end back end over lan and backend data is SQL server driver version 13 seems to be the most stable if that helps any maybe relevent.

 

[Minty]

Code - We need to see your code!
Copy and paste the delete button code here - make sure you enclose it in code tags (use the </> symbol in the editor above).

Also, you can simply paste a screenshot directly into these forums, no need to attach a photo of a screen.

 

[dtdukes86]

thank you for fast reply ill add to screen shots now,. u will see as a temp fix I'm closing the form forcing the user to reopen it and then it works but obviously this isn't what I want long term.

 

[dtdukes86]

note i i used docmd.runcommand accmddeleterecord this record is deleted but reapears on close and reopen form , that's why I'm using a del qry

 

[Minty]

Please don't paste pictures of code, paste the actual text, if anyone wants to edit it they have to type the whole thing out.

There is a lot going on there that makes no sense.
Why are you saving the form?
Then saving it again.
Why after deleting the record, are you then saving the record?

I would have thought the code should simply be

If me.locked = False Then
    DoCmd.RunCommand acCmdDeleteRecord
Else
   msgbox"The item is locked and can't be removed", vbInformation,"Unable to delete"
End if
Me.Requery

 

[arnelgp]

when they try to add a new row after a delete button has been pressed the primary key doesn't populate and therefore it throws up a load of errors when trying to save the new record. I've tried a hundred and one way to get it to populate using all kinds of vba save commands but it doesn't save with no id so I'm stuck why doesn't it populate when it works fine if I don't delete any records.

did you Define the primary key field when you create a Linked table? you should otherwise SQL won't determine the uniqueness of each records.

 

[sonic8]

as a temp fix I'm closing the form forcing the user to reopen it and then it works

Well, saving the form and the record (which one?) after deleting a record does make little sense to me.
A much more sensible operation would be to Requery the form and thus reflect the changes (deleted record) to the underlying data.

 

[Pat Hartman]

the issue is !! once the user has deleted any row they cannot add a new row until the order form is closed and reoped.

1. DoCmd.Save ..... Doesn't save data. It saves the form object. To save Data, you use
DoCmd.RunCommand acCmdSaveRecord
or
Me.Dirty = False
The users should NEVER be modifying forms so you would NEVER need the DoCmd.Save in your code.
2. Running a delete query to update the recordset bound to the form that is running the code is never right. Use the code suggested by Minty or use the form's RecordSetClone.
3. Compilers don't care if you bother to align your code in an orderly fashion but humans do. Proper alignment makes it ever so much easier to "see" the extent of an If statement.
4. NEVER write one line of code with multiple statements. Again, this is a human failing. Humans tend to miss one of the commands and that makes the code harder to understand.
5. Check the Master/Child links. That is what Access uses to populate the foreign key when a record is added using the subform.

 

[dtdukes86]

HI all sorry ive been out the office for a few days, the save commands was any attempt i could think of to make the data stick, the lock is incase the line is locked, the button being pressed is the delete record button so i want the record to delete providing it isnt locked, then the form to refresh and the user able to enter any new data they wish,,, this sounds simple and i agree the code is a mess but thats becuase the correct code simply didt work, im a little lost on where to do go from here , i didnt copy and paste the actuall code as i didnt know if it would display correctly aslo .

 

[dtdukes86]

the code provided by mintity isnt what i need as this would delete the locked reocrd which i dont want i cant use the delete command he sugests either becasue it remove it from view but the record reappears when you reopen the form somthing so simple as removing a record and its given me a right head ake.

 

[dtdukes86]

i will make a change rto code and repost what i would like it to be like haha ,, ignore code in comment

Private Sub delbtn_Click()

If Me.Locked = False Then
'DoCmd.OpenQuery "DeleteQryForDeletingStock(NewOrder)"
DoCmd.RunCommand acCmdDeleteRecord
MsgBox "Stock Levels will be auto updated to reflect this change, order will now close", vbOKOnly, "Info"

Else: MsgBox "Sorry item is locked and cannot be removed at this time", vbOKOnly, "Sorry"
End If


End Sub

 

[dtdukes86]

more annoyingly now the entire form seem to have gone into slow mode for some reason

 

[dtdukes86]

here the error i get, ,, note you only get this error if you try to add another product after using the del btn , in this example there was 2 lines oringinally, i deleted one line using the delbtn then tried to add a new product snd you can see if the red box there is no ID number therefore i get this error coming up, if i close the form between deleting and readding a new product the process works but you cannt delete then readd without closing form. if you delete or add reocords seperarelty without doing them in same instance both work fine i can add and delete just not add after delete,. hope that makles sense

 

[Mike Krailo]

And when you click on the debug button which line of code is it hung up on? You may have to step through it. Did you put the requery command in the code like Sonic8 suggested in post #7? You have to do that so the records on the screen properly update after deleting a record. What happens if you click off of that deleted record first, and then add a record?

 

[dtdukes86]

And when you click on the debug button which line of code is it hung up on? You may have to step through it. Did you put the requery command in the code like Sonic8 suggested in post #7? You have to do that so the records on the screen properly update after deleting a record. What happens if you click off of that deleted record first, and then add a record?

DoCmd.RunCommand acCmdDeleteRecord is highleghted yellow on bebug this i presume is becuase it cant save without a primary key, so its not the code itself at fault the issue lies with the record not getting the key it needs to be saved. very strange. when the key is a auto number field

 

[dtdukes86]

i will add all of the forms and there subform are based on qrys not tables themselves as the qureies cobine multi tables to provide the info

 

[dtdukes86]

just in reply to ur question mike i think i tried a reqeruy before and it works fine the record does disaprear if i use the delqry but not the accmd command. in any case it doesnt mater if i click the record 1st or move focus off the subform , untill i close and reopen main form i cannot add another record after the del button is pressed.

 

[Mike Krailo]

Without being able to directly do testing on this myself I can only offer that you add an error handler to trap that 3101 error. Then issue command to force the form to open in design view. Then another command to open it in normal view again. Much easier to troubleshoot if you upload the dB for further analysis.

 

[mike60smart]

just in reply to ur question mike i think i tried a reqeruy before and it works fine the record does disaprear if i use the delqry but not the accmd command. in any case it doesnt mater if i click the record 1st or move focus off the subform , untill i close and reopen main form i cannot add another record after the del button is pressed.

Hi
It would help if you could upload a zipped copy of the database with no confidential data.

 

[dtdukes86]

im not sure i can do that as this db uses 3 data links 1 to a local sql server and the other 2 link to 2 web based databases setup in phpmyadmin so without the data the forms dont really work and you wouldnt be able to test it very well. this single form is the main form order form in a massive database that does all sorts which I visit 3 times a week, and this issue is just bugging me that it cant get it working.