Just tested, the sample worked as expected. Did you enable code? Do you want to post your version of it so we can see what went wrong? I chose a couple of random items, entered 123 in the Other Value textbox and clicked on the button.
Just tested, the sample worked as expected. Did you enable code? Do you want to post your version of it so we can see what went wrong? I chose a couple of random items, entered 123 in the Other Value textbox and clicked on the button.
maybe this could work? I have to play
https://docs.microsoft.com/en-us/off...stbox.selected
My sample uses that, indirectly. You can loop it testing for selected being true or the simpler loop I used.
You would open tblOtherTable and hopefully see the record(s) that got added. I think it starts out empty.
Oh, and by enabling code I mean the option below the ribbon if the app is being run from an untrusted location.
I see, I have the warning disabled so it did not warn me but it blocked it indeed, now it works.
It's more or less what I want to do, I like the extra "other value" field, that can be useful.
It needs a deselect button.
Say you first pick several values and save them without "other value" and now you want to select a new one and add the "other value" to it, if you don't unslect the other ones first it will duplicate the EmpID and add the "other value" to all the new records. Can be cumbersome to click on the previously selected items to deselect if there are many of them.
I'll play with it and let you know how it went, probably next week. Thanks for your help.
reset something like this maybe? Replacing combo with the list box
Code:Private Sub cmdReset_Click() 'Reset Form button Me.cboPurchaseCat.Value = Null Me.cboReportsNames.Value = Null End Sub
You can add a simple loop to deselect everything after adding records. Something like:
Code:For i = 0 To ctl.ListCount - 1 ctl.Selected(i) = False Next i
No, that won't work with a multiselect listbox. Its value is always Null anyway.
I've created my queries and form
Unfortunately I can't just use a unique identifier like you did "EmpID", I need to save all my fields from the query to the new table and I added two "other" text fields.
My ListBox is called lstRequisitionItems and shows 10 columns I need to save to the table tbl_Manual_Requisitions.
I started debugging and have two issues for starter (I quoted out the text validation for now).
#1is returning a "User-defined type not defined" errorCode:Dim db As DAO.Database
#2 the multi selection from the list does not work (but it could be because the script bugs)
Code:Private Sub CmdAddRequisitions_Click() Dim strSQL As String Dim db As DAO.Database Dim rs As DAO.Recordset Dim ctl As Control Dim varItem As Variant On Error GoTo ErrorHandler Set db = CurrentDb() Set rs = db.OpenRecordset("tbl_Manual_Requisitions", dbOpenDynaset, dbAppendOnly) 'make sure a selection has been made 'If Me.lstRequisitionItems.ItemsSelected.Count = 0 Then ' MsgBox "Please select at least 1 requisition item" 'Exit Sub 'End If 'If Not IsNumeric(Me.txtQtyOverride) Then ' MsgBox "Please enter a numeric Qty" 'Exit Sub 'End If 'If Not IsDate(Me.txtDueDate) Then 'MsgBox "Please enter a Date MM/DD/YYYY" 'Exit Sub 'End If 'add selected value(s) to table Set ctl = Me.lstRequisitionItems For Each varItem In ctl.ItemsSelected rs.AddNew 'Query results rs!Component_ID = ctl.ItemData(varItem) rs!Description = Me.Description rs!Vendor = Me.Vendor rs!MOQ = Me.MOQ rs!Multi = Me.Multi rs!Lead_Time = Me.Lead_Time rs!Planned_Date = Me.Planned_Date rs!Suggested_Due_Date = Me.Suggested_Due_Date rs!Balance = Me.Balance rs!Suggested_Order_Qty = Me.[Order Qty] 'Open Fields rs!Manual_Order_Qty = Me.txtQtyOverride rs!Manual_Due_Date = Me.txtDueDate rs.Update Next varItem ExitHandler: Set rs = Nothing Set db = Nothing Exit Sub ErrorHandler: Select Case Err Case Else MsgBox Err.Description DoCmd.Hourglass False Resume ExitHandler End Select End Sub
I think I understand the problem, I just don't know how to fix it using your script.
Your script is adding new records to the original table (table A tblEmployees) as well as to the "other" table (table B tblOtherTable).
What I'm looking at doing is pulling records from table A (query --> List box) and insert the selected records to table B only, including with calculated fields from the query and manual entries (text boxes).
In my application table A is temporary (gets deleted and re-populated on refresh) and shows me items that will become negative within a defined period so it is a moving target, table B will be saved accumulating selections from table A which are items to be ordered.
The approach I am thinking about now is to add a checkbox and enter a yes/no in each line I want to save, then create an append query using the yes/no selection but it is not as pretty as your application of click to select.
My code should only be saving values to the "other" table. It only adds data to whatever table is specified here:
Set rs = db.OpenRecordset("tblOtherTable", dbOpenDynaset, dbAppendOnly)
I just tested that in the file from the website. You don't have the form bound to the employee table, do you?
You're right, I forgot that I manually populated the employee's table before so it appeared it was adding to the employee table.My code should only be saving values to the "other" table. It only adds data to whatever table is specified here:
Set rs = db.OpenRecordset("tblOtherTable", dbOpenDynaset, dbAppendOnly)
I just tested that in the file from the website. You don't have the form bound to the employee table, do you?
This being said I found a work around not needing coding by embedding a subform in a form then sending it to the "save table" with an append query. Actually I like it better because that allows me to enlarge the data selection range and then filter the dates as needed right on the form and I can input all my dates and quantities on 1 page all at once before saving, it's just not as nice but I'll try working on your option more when I'm done just for fun.