populate a subform with data searched and selected from listbox (1 Viewer)

[accessonly11]

dear all,

i have this purchase form with subform, when i search data from listbox, its temporary stores both two fields in temporary text box on right sides, and opon click on add button, both two values added in subform.

but problem is when i try to add another product, it shows error that and highligt the
DoCmd.GoToRecord , , acNewRec
please tell me where is the problem with code, or suggest me other better way

 

[MarkK]

Normally you just put a combo box on the ProductID field in the subform. That way the ProductID value is assigned immediately upon selection from the combo, and you don't need any code. To add a row, you just start adding data in the first empty row at the bottom of the subform.
hth

 

[moke123]

please tell me where is the problem with code, or suggest me other better way

I think we would need to see the code in order to do that. Even better would be a sample database.

 

[Pat Hartman]

DoCmd.GoToRecord , , acNewRec

Not sure why you are doing this. If you want to use the listbox to add a row to the subform - just run an append query. You know the ID of the parent. You know the ID of the product, you can use 1 for the quantity. Then after running the append query, requery the subform.
Me.subformcontrolname.Form.Requery

 

[accessonly11]

i am facing this problem, how can i fix it

i am using this macro behind add button click

 

[accessonly11]

I think we would need to see the code in order to do that. Even better would be a sample database.

before use of append query, i was on this code which were working partially. and giving me docmd.gotorecord , , acnewrec error. i giveup it, may be the append query is best way but if there is no error

'Me.Purchase_Data_Subform.SetFocus
'Me.Purchase_Data_Subform!ProductID.SetFocus
'DoCmd.GoToRecord , , acNewRec
'Me.Purchase_Data_Subform!ProductID = Me.txtProductID
'Me.Purchase_Data_Subform!Qty = Me.txtQty

 

[moke123]

You may be over complicating this.

Here's a very simple example

 

[accessonly11]

i am trying my best to make it simple not complicated, i am unable to where is the problem, this error is still on place.

 

[moke123]

Do you have the any tables open in design mode?

here's the error if you do...

It would appear you have tblPurchaseData opened in design mode.

 

[accessonly11]

Do you have the any tables open in design mode?

here's the error if you do...
View attachment 104101

It would appear you have tblPurchaseData opened in design mode.

No any other table or tab is open,
Moreover PurchaseData Subform is linked with Purchase Form with PurchaseID Autonumber field,

 

[accessonly11]

what is the solution plz

 

[Pat Hartman]

When you run an action query from a form and that query affects the table bound to the form, you MUST save the current record before running the action query.

In your case, the query should be run from the main form and update the table bound to the subform so unless you have some other form open or the table itself, there isn't anything obvious. the message is clear, you are conflicting with yourself.

Upload the db if you can.

 

[moke123]

As Pat says, We will probably need to see a sample database to see what exactly is going on.

Post a sample db with just the necessary tables and forms to see the problem.

 

[accessonly11]

my db is attached, i am using this code behind add button click


Private Sub cmdAdd_Click()
Dim strSql As String
If IsNull(Me.List390) Or Nz(Me.Qty, "") = "" Then MsgBox "Please select Product and quantity": Exit Sub 'make sure you have a product and quantity

strSql = "Insert into tblPurchaseData(ProductID,qty) values(" & Me.ProductID & "," & Me.Qty & ")"
Debug.Print strSql ' see what the strSql resolves to for trouble shooting
CurrentDb.Execute strSql, dbFailOnError 'execute the sql
Me.PurchaseDataSubform.Requery 'requery the subform
Me.ProductID = Null 'set list and quantity to nothing
Me.Qty = Null
End Sub



in which this code CurrentDb.Execute strSql, dbFailOnError get highlited and pop up error 3008, saying" the tblpurchasedata is already open....."

secondly i am also avoiding append query becase i dont want forms autosave property (maybe append query cant be reverse), for that all codes need to be written beforeupdate events, but i am low in knowledge.

 

[Pat Hartman]

Your logic is flawed and you are trying to avoid the best feature of Access which is bound forms. You are creating purchase orders and therefore the Products should be limited to those ordered from the specific supplier.

I don't understand why you don't want records to be saved as you add them to the order. The user can always remove an item or fix the quantity if he needs to before the order is transmitted to the supplier later.

1. Naming all your PKs "ID" is not cool nor is it useful. Using appropriate names makes the schema much easier to "see" without having to open the relationship diagram.
2. This form can be designed in two ways and it depends on which will be more efficient for the users. I'll go into the options below
3. The standard price belongs in tblProducts. Otherwise, you need to type it in for each order.
4. The product combo on the subform, needs to be filtered by the SupplierID on the main form so that only products ordered from that supplier show in the list.
5. Why do you have the tab stop set to no for the subform controls?
6. Dirtying the record in the load event is wrong. Only the user should dirty a record. If you want to open a form in either add view or edit view, use a menu that lets you do that. Do NOT put code in the load event to force it.
7. Writing multiple instructions on a single line is also not cool or efficient. It just makes your code hard to read.

Option 1.
Only data validation code is required
Remove the add button and remove the unbound search and qty fields as well as the product listbox.
In the data subform, remove the unbound controls and the FK to the parent form and the ID of the subform. They don't need to be visible.
Option 2.
Code required to control the interface as well as validation code.
Instead of using an add buttton, use the double click event of the list box.
Remove the unbound search and qty boxes.
Add code to the dbl click event to open an input box to get the quantity.
Verify the qty. Then run the append query to add the row to the data table. The append query needs 3 fields = ProductID, qty, and PurchaseID. The PurchaseID is required to connect the row to the order.

I fixed up the some of the problems with the tables like removing the 0 default for IDs and making certain FK's required. I also fixed the entry form to work the way I think it should work which is option1. Feel free to unhide the objects and make the changes I suggesedd if you really want option2.

 

[accessonly11]

Your logic is flawed and you are trying to avoid the best feature of Access which is bound forms. You are creating purchase orders and therefore the Products should be limited to those ordered from the specific supplier.

I don't understand why you don't want records to be saved as you add them to the order. The user can always remove an item or fix the quantity if he needs to before the order is transmitted to the supplier later.

1. Naming all your PKs "ID" is not cool nor is it useful. Using appropriate names makes the schema much easier to "see" without having to open the relationship diagram.
2. This form can be designed in two ways and it depends on which will be more efficient for the users. I'll go into the options below
3. The standard price belongs in tblProducts. Otherwise, you need to type it in for each order.
4. The product combo on the subform, needs to be filtered by the SupplierID on the main form so that only products ordered from that supplier show in the list.
5. Why do you have the tab stop set to no for the subform controls?
6. Dirtying the record in the load event is wrong. Only the user should dirty a record. If you want to open a form in either add view or edit view, use a menu that lets you do that. Do NOT put code in the load event to force it.
7. Writing multiple instructions on a single line is also not cool or efficient. It just makes your code hard to read.

Option 1.
Only data validation code is required
Remove the add button and remove the unbound search and qty fields as well as the product listbox.
In the data subform, remove the unbound controls and the FK to the parent form and the ID of the subform. They don't need to be visible.
Option 2.
Code required to control the interface as well as validation code.
Instead of using an add buttton, use the double click event of the list box.
Remove the unbound search and qty boxes.
Add code to the dbl click event to open an input box to get the quantity.
Verify the qty. Then run the append query to add the row to the data table. The append query needs 3 fields = ProductID, qty, and PurchaseID. The PurchaseID is required to connect the row to the order.

I fixed up the some of the problems with the tables like removing the 0 default for IDs and making certain FK's required. I also fixed the entry form to work the way I think it should work which is option1. Feel free to unhide the objects and make the changes I suggesedd if you really want option2.

thanks for your deep observation on db, i know there is lot of issues, against rules and regulations, i am working hard to bring things on order. most valuable people like you on that forum provide me lot of knowldge which i have forgotten 10 years ago. when the db get completed, i will share screenshots here to get your valuable opinions again.

 

[Pat Hartman]

You're welcome