I have two tables: Products and Invoice. Product table is for creating product list which will have additional information about products for calculations latter. Invoice is table which contains information about invoices and bought products. Invoice table column Product is combo box which I created that user wouldn't type same product in 100 variations (list is from table product column product description).
One invoice can contain more than one product which is not in product. NotinList add notinlist products to list but only one product at time (at least I know only this way).
Is it possible to add multiple records with notinlist? what i would like to to is:
1. user adds new invoice and access doesn't bothers with error messages about item not in list
2. after invoice have been added, access checks if all product is in list and offers to add those ones which is not in list to add to list.
Code: SELECT S.Key, S.Spec FROM tblSpecs AS S ORDER BY S.Spec;
NotInList code:
Code: Private Sub cboSpecs_NotInList(NewData As String, _ Response As Integer) ' MODIFY SPEC ENTRY. 10 Dim db As Database
[Code]....
1) For some reason, txtMessage is not displayed on the form when NotInList is processed. I see it get populated properly in debug mode, but it never gets on the form. But txtMessage gets displayed normally in Add and Delete events.
It seems to do with the way NotInList works because txtMessage is displayed if I use acDataErrContinue. But that's not what I want; it leaves the combobox list open and can't save record if I close the form.
2) DLookup is used to extract the old Spec value to display in the message. I can see the old Spec in debug mode in cboSpec.column, but Listindex is -1 and unusable.
I don't know how to point to the proper record in the list. The only other way I can think of is to loop through the list comparing the Key, and then extract the Spec.
I don't know if it's more efficient to use lookup or loop.
I have a form frm_GlobalSettings with a combobox cmbDescription that finds a record based on the value selected. The row source type for cmbDescription is Table/query, and the row source is a select statement on the form's underlying table.
I want the user to be able to use cmbDescription to add a new record to the form's underlying table. I currently have
Code:
Private Sub cmbDescription_NotInList(NewData As String, Response As Integer) Dim strTmp As String 'Get confirmation that this is not just a spelling error. 10 On Error GoTo cmbDescription_NotInList_Error 20 strTmp = "Add '" & NewData & "' as a new global setting?"
[code]....
but when a new value is entered this errors on line 70 with "runtime errror 2118 - you must save the current field before you run the requery action".I've tried various ways around this but can't get it to work.
I have a form with 15 unbound text boxes (daily temperatures) and what I am trying to do after entering the temperatures into the text boxes the user clicks an add button which will add 15 new records into the temperature table
If I want to delete multiple records (select more than a record and right-click delete) how can I achieve this
given the following with one record:
Private Sub Form_Delete(Cancel As Integer) DoCmd.RunSQL "Delete * from Trial where TrialID =(Text40)" Me.Refresh End Sub
PS:- I am trying to solve the cannot update /delete because of "modifications due to multiple base tables "issue and I am not sure if attempting to add in the On delete event would fix it (instead of an instead of delete trigger since it would be useless to setup triggers with one table to update ). I am thinking of doing the same with update in the after update event.
I have a list box populated with record ID's all of which need a date field updated. I have been succesful at using the list box to update single records, but am not sure how to transfer this idea to work with multiple records simultaneously.
The code i am using is:
Private Sub Command13_Click() Dim i As Integer Dim strSQL As String Dim sMessage As String Set db = CurrentDb() Dim sTitle As String For i = List10.ListCount - 1 To 0 Step -1
I have a form that the users enter data into and send a report each day. I would like that in a click of a button all the data I entered the day before and that have ="Open" will be entered into the tables with today's date. Is that possible?
I have two table. Table 1 (assets) list all my assets etc. Table 2 lists all servicing for each asset.
I have a form which generates a list box of items that require servicing (once servicing has been completed on these items) I would like to update all records, in both tables. Table 2 with all the information about the service. and Table 1 with (only) the next service date.
I have set the list box to allow multiple selection and have used the following code to allow new records to be update in table 2 (which works). but I cant seem to get the code to edit one cell in table 1.
private Sub Command59_Click() Dim strSQL As String Dim db As DAO.Database
Instead of using an update query, so my Audit Trail will continue to update correctly, I'd like to use an input box and run a function that performs much like an update query.
My problem is, only one out of 4 records updates correctly.
I have a few hundred access databases all with the same tables and structure, and want to import all the data in a 'mother' database (which i created with the same tables and structure).
I am using a sub that feeds a string to another public sub as follows:
Code: Private Sub cmdImport_Click() Dim strFolder As String 'source folder path for import Dim strFile As String 'individual file path strFolder = GetFolder(Environ$("USERPROFILE") & "Documenti") If Len(strFolder) <= 1 Then Exit Sub
I have a form (Form4) which has a a list box (list11) that allows multi select. When I select on the records I need I hit a button that opens a form up with the selected records. This form is a continuous form. I have an unbound combo box (Combo55). Its values come from a specific table. I want to hit a button and change the field "Assigned" on all the records showing to the value that I selected in Combo55.
The problem is it only changes the value of the currently selected record. How Do I get it to change the value of all records that are showing?
I am only testing this idea with default field names. Before I implement the database I have to get a working model and present it. So I am building this and naming at the moment isnt important.
1. How many employees received more than one award (answer: 2). 2. How many employees who received the Nobel prize also received any second prize (Answer: 2). 3. How many employees who received the Nobel prize also received the Fulbright (answer: 1).
I'm trying to create a report that's based on a query, and the query has three fields: [PersonName], [PersonDate], [PersonShift]. This table holds records for people that worked on certain days and certain shifts. What I want to do is create a report that gives a graphical calendar display of each day in a month, and on any day that the person has a record (and sometimes there are more than one), I'd like to see just the PersonShift records showing in that day's box.
tblPersons PersonName PersonDate PersonShift Jason 4/10/14 FIRST Jason 4/13/14 FIRST Jason 4/13/14 SECOND
So if I were to print this report for Jason, I'd get all the days in April laid out like a calendar, and on 4/10/14 you'd see "FIRST" in the box, and on 4/13/14. you'd see FIRST and SECOND in the box. All the other boxes would be blank.how to display the calendar, how to display the dates. I'm able to return records to those boxes by creating 31 separate queries, one for each day of the month, and each query returns records for that day. The queries are added to the report as subreports. It all works beautifully.
The thing is, I'm running 31 queries every time I pull the report. Is there a way to code a single field on a report that will run a SELECT statement on the table using variables that are located in fields elsewhere in the record?
If I have a PersonName field on the header of the report, and I have a PersonDate field in the detail of the report's record, can I create a new field in the detail of that record that runs a SELECT statement on qryPersons, and filters the tblPersons by the PersonName on the header of the report and on PersonDate in the record?
I want a field on a report that runs a SELECT statement on tblPersons, I want the field to return only the PersonShift records for that person based upon the PersonDate. Each of the fields on my report have a CalendarDate field, and I want the SELECT statement to return records where the PersonDate matches the CalendarDate, and again, it should only display PersonShift records.
I am looking for a way to enable a user to fill in a number of fields on a form press a button(CreateButton) and duplicate the records how ever many times is stated in a text box(TxtQty). I also need this to increase the serial number by the amount of times stated in the text box.
There will need to be another text box (TxtNextSerialNumber) stating the next first available serial number
For Example: TxtNextSerialNumber = AD-Oracle-00010 TxtQty = 5 Press CreateButton to create 5 records TxtNextSerialNumber = AD-Oracle-00014
5 new records created with the below fields duplicated and the above happening.
I will then need a message box informing the user of the serial numbers created:
'You have created serial numbers AD-Oracle-00010 to AD-Oracle-00014'
Table Name: ADOracle Form Name: ADOracleTestData
Fields Names: CustomerName: Duplicate PartNumber: Duplicate OrderNumber: Duplicate OrderDate: Duplicate HoseKit: Duplicate Returns: Duplicate Comments: Duplicate SerialNumber: + the amount shown in TxtQty starting on next serial number available.
I am using the following code to check for duplicate tickets when importing multiple records into a datasheet view form by using the paste append function.
Code: Private Sub Ticket_Number_BeforeUpdate(Cancel As Integer) DoCmd.SetWarnings False If DLookup("Ticket_Number", "Record_Store", "Ticket_Number= '" & Me.Ticket_Number.Value & "'") > 0 Then Cancel = True MsgBox "There were import errors, please open View Import Errors above." End If End Sub
The form is used to insert multiple records into the database at a single time.
That codes works to check for duplicates. And if there are none there are no popup messages.
If there are duplicates though it gives a popup for every single Ticket_Number that is a duplicate.
I am wondering if there is a way for it to give only a single popup once it completes checking all the records to be imported for duplicates.
Custom filter I'm trying to set up. I have a data full of records with multiple columns and a form linked up to it. The form has a search box that works as well as navigation buttons.
Currently I am trying to get a filter to work. The filter will use up to five combo foxes to narrow down the fields, then return the filtered records after clicking a button. The button itself is where my code is. My issue now though is I cannot get the filter to work if I try to use more than one combo box.
The code I have at the moment is as follows: '------------------------------------------------------------ ' cmdApplyFilter_Click ' '------------------------------------------------------------ Private Sub cmdApplyFilter_Click()
End SubThe current error I am getting is Error 13: Type mismatch. I also know that the And might be the cause but I cannot change it to AND: it keeps changing back.
I work for a company that manufactures home appliances & electronics. When those products fail within the warranty period, we are obligated to repair the units for the customers.
We have around 200 factory technicians who make those repairs. Each tech is able to make approximately 8 repairs each day. We have a dispatching system that assigns the repairs to the techs based on their availability each day. But the problem is that we have to manually enter and adjust the schedule for all 200 techs every single day, and this takes a lot of time. And of course technicians get sick, take vacation, etc, so we have to adjust the schedule so no techs will be assigned calls when they're off.
The problem: For example, currently when a technician takes off for 2 weeks, the user has to enter 14 individual records for the tech, which is somewhat time consuming. What I need is to program a button click event to determine the two dates (startDate & endDate) and append multiple records from one single entry in the form for each date in between and including the two date fields.
I know I'll probably need to create a loop that will loop through the two dates on the form and append a record for each day, so I can then cross reference the dates to the master schedule dates to make sure that no availability is opened for the techs taking time off.
I added the notinlist event to an unbound combo (I use access 2003) and used the ADO code but no matter what I try I can't get it working. Out of desperation I substituted the DAO code and "bingo" I have a working combo which adds any data I type in etc, no errors no problems. My question now is; Do I now stick with the old rule "if it ain't broke don't fix it" and continue using the DAO code or do I continue to pull my hair out (not much left now) trying to get the ADO code working?
This is the NotInList I have been using for some time (below). it works well but only updates one field. I need to update two fields now, City and ZipCode. Can I add data to two fields each time the notinlist is envoked?
On Error GoTo Err_ErrorHandler Const Message1 = " Is Not In The Database." Const Message2 = "Would you like to add it?" Const Title = "Unknown entry..." Const NL = vbCrLf & vbCrLf Dim db As DAO.Database Dim rs As DAO.Recordset If MsgBox(NewData & Message1 & NL & Message2, vbQuestion + vbYesNo, Title) = vbYes Then Set db = CurrentDb Set rs = db.OpenRecordset("MyTbl or Qry") With rs .AddNew .Fields("MyField") = NewData .Update .Close End With Response = acDataErrAdded Else Me.MyField.Undo Response = acDataErrContinue End If Exit_ErrorHandler: Set rs = Nothing Set db = Nothing Exit Sub Err_ErrorHandler: MsgBox Err.Description, vbExclamation, "Error #" & Err.Number Resume Exit_ErrorHandler
hey Well I'm haveing some problems here. When I type something in my combobox that aint in the list it doesnt go to the precedure NotInList.
I need this for both of these comboboxes and I tried an altarnative way with rowsource but that aint working aswell so here is the code
Private Sub cboReeks_AfterUpdate() If cboReeks.Text <> cboReeks.RowSource Then Call Foutmelding("U hebt een foutieve waarde ingevoerd") cboReeks.Value = "" End If End Sub
Private Sub cboRonde_NotInList(NewData As String, Response As Integer) Response = 0 Call Foutmelding("U hebt een foutieve waarde ingevoerd") cboRonde.Value = "" End Sub
Hi all. This is the code that I have used in my NotInList event
Private Sub Suppliers_NotInList(NewData As String, Response As Integer) On Error GoTo Err_Suppliers_NotInList
'-- We may need to add another Product Response = MsgBox("[" & NewData & "] is not yet a valid Supplier..." & vbCr & vbCr & _ "Would you like to add this Supplier to your DataBase?", vbYesNo)
If Response = vbYes Then '-- Create a new Product record Dim db As DAO.Database Dim MySQL As String Set db = CurrentDb() MySQL = "Insert Into TableSupplier (Suppliers) " & _ "Values(""" & NewData & """)" db.Execute MySQL, dbFailOnError Set db = Nothing Response = acDataErrAdded '-- will requery!! Else Response = acDataErrContinue End If
Exit_Suppliers_NotInList: On Error Resume Next Set db = Nothing Exit Sub
Err_Suppliers_NotInList: MsgBox "Error No: " & Err.Number & vbCr & _ "Description: " & Err.Description Resume Exit_Suppliers_NotInList End Sub
The problem i'm having is that the code is not working. However, I have used the same code but modified to suit on another field elsewhere in the form which works fine. Any ideas????
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8 Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.
I have one table with the following field and data:
ItemId Red Yellow Green Multiple Inventory position 0001 10 30 50 5 45 0002 5 40 47 5 23 0003 11 20 30 10 5
I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click() Dim strSQL As String Dim RevisionDate As String Dim RevisionRevisedBy As String Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
OKay so i have a combobox with 2 fields, City and CityCode. (i.e. Albany, ALBA) I want to be able to type in a new citycode or city so that when it's not in the list the user can add it to the list along with the field that they're not entering which would be city or citycode. Please help! thanks
Hi All, I'am using the following code to update a table (tbl_CDT) on a NotInList event of a cboBox (CDT) I keep getting a SQL syntax error message, can anyone help resolve the code please
Private Sub CDT_NotInList(NewData As String, Response As Integer)
On Error GoTo CDT_NotInList_Err Dim intAnswer As Integer Dim strSQL As String intAnswer = MsgBox("The CDT " & Chr(34) & NewData & _ Chr(34) & " is not currently listed." & vbCrLf & _ "Would you like to add to the list now?" _ , vbQuestion + vbYesNo, "DentureBase") If intAnswer = vbYes Then strSQL = "INSERT INTO tbl_CDT's([CDT_Name]) " & _ "VALUES ('" & NewData & "');" DoCmd.SetWarnings False DoCmd.RunSQL strSQL DoCmd.SetWarnings True MsgBox "The new CDT has been added to the list." _ , vbInformation, "DentureBase" Response = acDataErrAdded Else MsgBox "Please choose a CDT from the list." _ , vbInformation, "DentureBase" Response = acDataErrContinue End If CDT_NotInList_Exit: Exit Sub CDT_NotInList_Err: MsgBox Err.Description, vbCritical, "Error" Resume CDT_NotInList_Exit
