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')
View Replies
ADVERTISEMENT
Feb 20, 2013
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.
View 1 Replies
View Related
Feb 13, 2014
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)
[Code] .....
View 3 Replies
View Related
Sep 13, 2014
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.
Code:
Me.Date_and_Time_Complete_Package_DUE = DateAdd("h", [SLA_Time], [Date_Complete_Package_Received])
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.
Code:
Me.Date_and_Time_Complete_Package_DUE = WorkHours(DateAdd("h", [SLA_Time], [Date_Complete_Package_Received]))
I have a Workhours module and it is used for a lot of things and it works perfectly.
View 1 Replies
View Related
Aug 15, 2013
I would like to add image to list box but i read somewhere i have to use continuous sub form instead.
Now i like to know in sub form also we can select multi items and it shows highlighted entire of each row and easily getting value if selected items.
View 4 Replies
View Related
Sep 27, 2012
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?
View 6 Replies
View Related
Feb 27, 2015
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.
View 10 Replies
View Related
Feb 17, 2015
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.
Here is the SQL from my query:
INSERT INTO Issues ( Customer, Title, [Due Date], [Opened By], [Opened Date], Priority, Comments, [Job Number], Description )
SELECT [TBL003_Combined Data].CUSTOMER, [TBL003_Combined Data].TITLE, [TBL003_Combined Data].[DUE DATE], [TBL003_Combined Data].[OPENED BY], [TBL003_Combined Data].[OPENED DATE], [TBL003_Combined Data].PRIORITY,
[Code] ....
And here is how I "thought" I could fix it through Access VBA:
Function UpdateIssuesDatabase()
Dim con As Object
Set con = CreateObject("ADODB.Connection")
On Error GoTo UpdateIssuesDatabase_OpenError
con.Open _
[Code] .....
I have highlighted where it is giving me the issue.
View 9 Replies
View Related
Aug 6, 2014
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?
View 14 Replies
View Related
Dec 29, 2014
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.
View 6 Replies
View Related
Aug 20, 2015
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.
View 3 Replies
View Related
Jul 16, 2013
So I am using the following code:
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.
View 6 Replies
View Related
May 28, 2014
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 .
View 4 Replies
View Related
Apr 8, 2014
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?
View 2 Replies
View Related
Apr 30, 2015
Will archiving older data improve response time of a database?
What is the best way to archive data knowing that older records will still be accessed twice a year?
View 2 Replies
View Related
Jun 5, 2014
The following code gives me runtime error message "couldn't set the list property , Type mismatch".
Private Sub UserForm_Initialize()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ws1 As Worksheet
Dim j As Long
j = 0
[Code] ....
View 3 Replies
View Related
Sep 5, 2014
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.
View 10 Replies
View Related
Dec 14, 2014
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.
View 7 Replies
View Related
Jul 26, 2014
I've a Sub form as datasheet view/grid.
See Capture1 & Capture2.
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.
View 14 Replies
View Related
Aug 6, 2013
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
View 5 Replies
View Related
Jun 27, 2013
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).
View 6 Replies
View Related
Sep 12, 2013
I Export data by ID from Access to Excel.
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
[Code] ....
View 2 Replies
View Related
Aug 11, 2014
I am getting Run-time error '3464': Data type mismatch in criteria expression when i execute the code:
Option Compare Database
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Public verificacoes As Boolean
[code]....
View 5 Replies
View Related
Dec 9, 2004
Hello again,
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!
G
View 1 Replies
View Related
Apr 3, 2014
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.
View 4 Replies
View Related
Mar 28, 2014
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).
View 1 Replies
View Related
