Modules & VBA :: Adding Data From List Box To Database One At A Time
Sep 12, 2013
how do you loop through and insert selected data from a listbox on at a time?For example, lets say you have an insert statement that has a firstname, lastname, CarsID(foreign key) and address field. Lets say you had another table that has ID and CarsID(primary key) field. In the listbox, you have populated it with all the cars and they are selected.
Example:
INSERT INTO PEOPLE(firstname, lastname, CarsID) VALUES('John','Smith','Honda')
INSERT INTO PEOPLE(firstname, lastname, CarsID) VALUES('John','Smith','FORD')
I have been asked if I can add a priority list to my database and I am trying to figure out how to do it.
The database already exists and this would be added code/forms. Basically this database has a number of users, all identified by a login name. Each of these users have several projects assigned to them. The projects are identified by a unique project number.
What I need to do is to be able to list all the projects that a user has, which is easy to do by simply creating a query, but then the user needs to be able to type in a priority number (1 to infinity) for each of these projects with no number repetitions. If a project priority is updated, say from 6 to 2, then the projects that had priorities 2 to 5, get a +1 added to their priority number so they go 3 to 6. Likewise if a project gets "demoted" (priority is lowered) then the ones below go up one.
I have some code that creates appointment that i can send to colleagues, when I run the code first time it work all ok but the second time i run it i get a run time error see pic below
But I don't get any error's if i leave outlook open have also try the code on 2 pc's but stiil same problem
1392336756_tmp_run_time_error[1].jpg
Code: Shell ("Outlook.exe") Dim outMail As Object Set outMail = Outlook.CreateItem(olAppointmentItem) outMail.Recipients.Add (Me.txtsupervisor)
In my DB that we use and a workflow tool, some of our work has and due date and time.If we get the complete_package our work time starts and we have X amount of time to complete our work. This is something i worked on but set it aside, now i am coming back to try and fine tune this so it returns a more accurate value.
So if we receive and [Date_Complete_Package_Received] at 09/13/2014 09:00:00 AM and based on the work being done we have 5 hours to complete the work, then the [Date_and_Time_Complete_Package_DUE] would be 09/13/2014 02:00:00 AM. That part is simple and i have coding that does that just fine. [SLA_Time] is where it gets the amount of time allotted, we have 5,10,12,14,and 16 hours depending on what is being done.
This is done on AfterUpdate of a field on one of my forms and it works the way it is but what i need is to be able to run this through my Workhours Function so i am not getting values that our in off hours.The following doesn't work i know i can't use the Workhours function with the DateAdd but this is just to show what i am trying to do.
I have a list of 22 soccer players with their weights sorted from heaviest to lightest in numerical order. I want to add further players and have the list reflect their standing each time it changes i.e. Col 1 Player ID, Col2 FirstName, Col 2 SecondName, Col 3 Mass:90 Kgs Col 4 Position In group: 1
A new player registers and is 95Kg.....he becomes #1 and the rest go down etc. Can this be done?
Can I create a table in the back end of my database using VBA in the front end? My client is using this system and I can't easily go to their location. I need to create a table on the back end and have it available in the front end applications. My thought is to create a front end only for the purpose of updating the backend with this new table. My idea is now to have it open with a button for the user to click - this will add the table to the backend with the required fields, confirm the update was completed and then close. This app then would be trashed so it would not be run again. (Though I would put a check in the VBA that if the table existed, the user would get a message box and the coding stopped to avoid overwriting the existing file.)
And once the table is created, is there a way to programmatically link it to the front end. I will be providing a new front end with this update so I could add something in the new version.
The goal I am trying to accomplish using VBA within Access 2010 is to append/add certain records to another database (Customer Service) from my database (Client Request). I tried using a macro to append then update records in my database but there is a "lag" time as to when the information is updated in the 'Customer Service' database causing my updates not to occur.
I need to add trim to all text boxes in my database. Any easier way than to add it to the afterupdate event of every text box or a complete list of all trims on buttons that save the records?
I have a number of groups in a particular database. These are based on the month that they relate to, but they are also split into four separate groups for each month. So for example, I will have four groups for November 2014:
November 2014 Raw Data (which contains linked tables in another database) November 2014 (which contains the bulk of the queries that are used for processing) November 2014 Reporting (which are the queries that are used for month end reporting - obviously) November 2014 Misc (anything that doesn't fit into the above, although there are always queries in these groups)
Now, I have been using this database since 2012, so as you can imagine there are quite a significant number of groups. Adding queries to new groups for each new month is therefore starting to take a while by scrolling down to the bottom... Is there a way to do this by using VBA? It would save a bit of time each month.
I am trying to add extra validation in a sample db (done in A97 (converted to A2003)see attached.
I am attempting the following: Please not that the db has existing validation which I would like to keep (or change if advised) ...
Conditions: 1. Input into field "ID" must start with the letter P or p or R or r 2. If field "ID" starts with the letter P or p and field "Code" starts with the letter R or r, then a message stating that this combination is invalid should appear 3. If field "ID" starts with the letter R or r and field "Code" starts with the letter P or p, then a message stating that this combination is invalid should appear.
Code: Declare Function WNetGetUser Lib "mpr.dll" _ Alias "WNetGetUserA" (ByVal lpName As String, _ ByVal lpUserName As String, lpnLength As Long) As Long
[code]...
My question is how can I create a table listed with numbers as an access list? Right now only the ######## has access but I'd like to create a table for numbers who will have access.
In the access form I want to add a button with a special functionality.
After clicking "Select file" button special window should be open (or something else). It should give user a possibility of files locating (doc, pdf, rtf, txt ...).
After selecting the file and confirm the choice in the table "File_location" in database path to the file should be saved .
Looking for (semi) generic piece of code that knows how to handle (semi) random XML files and structures, preferably creating tables and columns on the fly...
Or some code that will read an XML tree and genericaly will add the data to the tables it can find?
I work on a pre-created Access database, and the other day I was working on it, and was trying to export something to Excel to sort it and do some Pivot analysis.
Anyway, I must have pressed something, because now every time I open the database, rather than saying "record 1 of 20463" and showing the data from record 1, it shows "record 1 of 1" and all the data fields are blank. If I go to "Records" and "Show All Records" they'll all come up, but I don't want to have to do that every time, and as I import and export all the time, I'm worried that the next time I try it it'll mess up the years of data I have.
I have a form for entry and some fields are computed or result of a query from another table. I have a function that looks up a value from another table like so
************************************************** ******** Public Function GetTargetType() As Variant GetTargetType = DLookup("type", "tblFormulations", "[tblFormulations!formulation]=Forms![frmNmsConsumptionEntry]![formulation]") End Function ************************************************** ********
Which works fine when I test in the immediate window.Then I have this form event. This however does not insert this value when I am adding records using my continuous form.
************************************************** ******** Private Sub Form_BeforeInsert(Cancel As Integer) Me!target_group = GetTargetType() 'Forms!frmNmsConsumptionEntry!target_group = GetTargetType() '[tblNmsConsumption.target_group] = GetTargetType() End Sub ************************************************** ********
making sure I can insert this value once retrieved.
I'd like to prevent for duplicate time/date for same GuideCode. How can i prevent for those during the entry time or save time ? Is it possible ?
Capture1.
Capture2.
1. AUNKZ(GuideCode) is already assigned in other file number(12345) for 03-Aug-14 (as 02-Aug-14 to 04-Aug-14).
2. TOD(GuideCode) is already assigned in same file number(23456) for 10-Aug-14, 12:00 PM (as 10-Aug-14 to 10-Aug-14, 8:00AM to 1:00PM). I'd like to prevent if the file number are same or not same.
I've got a data entry form bound to one table. The form has four buttons:
- Clear Fields - Cancel - Save and exit - Save and add another (which should save the user input to the subform/table, clear the input fields, and allow the user to add another record)
I can't quite seem to get the "Save and add another" button to work. When I put some information in the input fields and click the button, it saves it to the subform/table perfectly, but when I try to do it again, it just edits the last record (the one just created).
How can I get that button to place the information from the input fields in a new record every time?
The _Click event for the button looks like this:
Code: If Len(Me.field1 & Me.field2 & Me.field3) > 0 Then Me.Refresh btnClear_Click DoCmd.Save End If
I have a variable (dtDueDate as Date) showing as 6/28/2013. I want to append a time value to it (dtMaxTime as Date) which is 5:00 PM so dtDueDate reads 6/28/2013 05:00:00 PM. I have been going in circles trying to figure this out. My goal is to append this date to a table field which has a datetime (General Date).
Is it possible that two mgsbox will Show up, where the user can put in the timeperiod.
The time period would be the starting day of the contract.
It's called inception_date
Code: Private Sub Befehl1_Click() Dim xlApp As Object 'Excel.Application Dim xlBook As Object 'Excel.Workbook Dim xlSheet As Object 'Excel.Worksheet Dim rstID As DAO.Recordset, tmpStr As String Dim rstGr As DAO.Recordset, strSQL As String
I think my subject heading explains it. How would you add or subtract two short time formatted values and produce a short time value? Also, how would you change a short time value into number minutes. (i.e. 5:30 into 330) Thanks!
I have an attendance database and I connect the time attendance machine db to my access db, what i am trying to do is to generate a report that shows the time in and time out for specific date. the type of attendance db is date/time.
Please see the attached screenshot db from attendance machine.
an also some time there is duplicate entry, I need to get the first and the last entry only for specific date.
I have two databases, both have mysql backened and access frontend.
In first database, I'd like to access the second database, check if that person has an entry in the second database and then fetch that data into the first database. To make it easier, data come from different tables (though I'd probably access only one entry in each table)
Not sure where to start, the only idea I have is to link tables from both databases into the frontend ad then it's kinda easy (would have to rename the tables).
