User form that allows me to verify info in a table Access
I am trying to create a user form that will return information based on the bar-code i type in and allow me to make sure the prices are matching and then add one to the amount column not sure whether i should use access or excell and how to go about it
While your question is a little lacking in detail, I think what you are asking is whether you should use Access or Excel for something like this. In this case, since it is a price checking tool and inventory editor, I would recommend Access. This is because it is easier for users to "break" the Excel form (accidentally overwrite a price, delete a product bar code,etc) than in Access. I am just learning access myself, but it should be possible to create a query that checks an entered barcode and price and checks it against a table. Similarly, it should be possible to create a query that edits the number of a barcode in stock. Good Luck!
See also questions close to this topic
Indexing needs to be sped up
I have a table with the following details:
CREATE TABLE `test` ( `seenDate` datetime NOT NULL DEFAULT '0001-01-01 00:00:00', `corrected_test` varchar(45) DEFAULT NULL, `corrected_timestamp` timestamp NULL DEFAULT NULL, `unable_to_correct` tinyint(1) DEFAULT '0', `fk_zone_for_correction` int(11) DEFAULT NULL, PRIMARY KEY (`sightinguid`), KEY `corrected_test` (`corrected_test`), KEY `idx_seenDate` (`seenDate`), KEY `idx_corrected_test_seenDate` (`corrected_test`,`seenDate`), KEY `zone_for_correction_fk_idx` (`fk_zone_for_correction`), KEY `idx_corrected_test_zone` (`fk_zone_for_correction`,`corrected_test`,`seenDate`), CONSTRAINT `zone_for_correction_fk` FOREIGN KEY (`fk_zone_for_correction`) REFERENCES `zone_test` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
I am then using the following query:
SELECT * FROM test WHERE fk_zone_for_correction = 1 AND (unable_to_correct = 0 OR unable_to_correct IS NULL) AND (corrected_test = '' OR corrected_test IS NULL) AND (last_accessed_timestamp IS NULL OR last_accessed_timestamp < (NOW() - INTERVAL 30 MINUTE)) ORDER BY seenDate ASC LIMIT 1
Here is a screenshot of the optimiser - the
ORDER BYis slowing things down, and in my opinion seems to be indexed properly, and the correct index (
idx_corrected_test_zone) is being selected. What can be done to improve it?
ACID - durability - how it work in case of systeme failure between confirmation and data being transmitted to the store.
"...If a system fails between the confirmation and the data persistence, the transaction log is synchronized with the persistent store to bring it to a consistent state."
My question is, what does it mean - I'm confused. "bring it to a consistent state"?
Does it mean, in case of systeme failure, the data base are going to be update and synchronise with log, after failure ? data going to be rewriten to the store?
See if used "used" an item
Let’s say I have two databases, tblUsers & tblItems. TblUsers will store the user ID, name, age etc tblItems will store ID, description,..
Now, I want users to be able to add new items inside tblItems (this was not problem and works without a problem). But I want user to click on an item and say “I used this”.
An example: tblItems got 7 items (ItemID1, ItemID2, ItemID3,.. ) user A with userID1 used item 2,4 and 5 (so ItemID2, ItemID4 and ItemID5) Now I want to show this on a new page, where it says “User A, used item 2 with description, item 4 with description and item 5 with description”
So I want users to add new items to the tblItems database, but I also want them to say if they used it yes or no. and if yes, I want to show them witch they used and witch they didn’t.
VBA: Combine two cells to form a series name
I want to combine two text pieces in two different cells to form the name of a series. There should be a blank space between both pieces. Unfortunately, I get a
runtime error 1004
Dim Dataws As Worksheet Dim Chtws As Worksheet Set Dataws = ThisWorkbook.Sheets("Overview") Set Chtws = ThisWorkbook.Sheets("Chart") Set ChtObj = Chtws.ChartObjects("ChartA") With Dataws CurrentRow = 13 Do Until Range("A" & CurrentRow) = "" CurrentRow = CurrentRow + 1 Loop End With With ChtObj Set Ser = .Chart.SeriesCollection.NewSeries With Ser .Name = "=" & Dataws.Cells(CurrentRow, 4).Address(False, False, xlA1, xlExternal) & " " & Dataws.Cells(CurrentRow, 1).Address(False, False, xlA1, xlExternal) .XValues = "=" & Dataws.Cells(CurrentRow, 5).Address(False, False, xlA1, xlExternal) .Values = "=" & Dataws.Cells(CurrentRow, 6).Address(False, False, xlA1, xlExternal) End With End With End Sub
How to run an automated plot macro for a whole folder in Excel VBA
Hey I have a work problem. I get quite some data with a lot of information. This data is typically a CSV.-File. Now I already have code to change all the files (same folder) into .XLS-File. My second code creates a linegraph but only for the "open" file. My question is, how can I make it to do it for the whole folder (to run the plot code)? And also how does the chart get the title of the file? I tried with
ActiveChart.ChartTitle.Text = ActiveSheet.Namebut I get only "Diagramm X". Also is it possible to combine all codes and not run first code1 from cvs to xls and then the code that creates the graph?
Code for .CVS to .XLS:
Sub CSVtoXLS() 'UpdatebyExtendoffice20170814 Dim xFd As FileDialog Dim xSPath As String Dim xCSVFile As String Dim xWsheet As String Application.DisplayAlerts = False Application.StatusBar = True xWsheet = ActiveWorkbook.Name Set xFd = Application.FileDialog(msoFileDialogFolderPicker) xFd.Title = "Select a folder:" If xFd.Show = -1 Then xSPath = xFd.SelectedItems(1) Else Exit Sub End If If Right(xSPath, 1) <> "\" Then xSPath = xSPath + "\" xCSVFile = Dir(xSPath & "*.csv") Do While xCSVFile <> "" Application.StatusBar = "Converting: " & xCSVFile Workbooks.Open Filename:=xSPath & xCSVFile ActiveWorkbook.SaveAs Replace(xSPath & xCSVFile, ".csv", ".xls", vbTextCompare), xlNormal ActiveWorkbook.Close Windows(xWsheet).Activate xCSVFile = Dir Loop Application.StatusBar = False Application.DisplayAlerts = True End Sub
Code for the plot I adapted from this
Sub Test() Dim LastRow As Long Dim Rng1 As Range Dim ShName As String With ActiveSheet LastRow = .Range("B" & .Rows.Count).End(xlUp).Row Set Rng1 = .Range("B2:B" & LastRow & ", C2:H" & LastRow) ShName = .Name End With Charts.Add With ActiveChart .ChartType = xlLine .HasTitle = True .ChartTitle.Text = "ActiveSheet.Name" .SetSourceData Source:=Rng1 .Location Where:=xlLocationAsObject, Name:=ShName End With End Sub
Sub ProcessFiles() Dim Filename, Pathname As String Dim wb As Workbook Pathname = ActiveWorkbook.Path & "\Files\" Filename = Dir(Pathname & "*.xls") Do While Filename <> "" Set wb = Workbooks.Open(Pathname & Filename) DoWork wb wb.Close SaveChanges:=True Filename = Dir() Loop End Sub Sub Test(wb As Workbook) Dim LastRow As Long Dim Rng1 As Range Dim ShName As String With ActiveSheet LastRow = .Range("B" & .Rows.Count).End(xlUp).Row Set Rng1 = .Range("B2:B" & LastRow & ", C2:H" & LastRow) ShName = .Name End With Charts.Add With ActiveChart .ChartType = xlLine .HasTitle = True .ChartTitle.Text = "ActiveSheet.Name" .SetSourceData Source:=Rng1 .Location Where:=xlLocationAsObject, Name:=ShName End With End Sub
But I get errors that I don't understand because I am new to VBA. The code states "Sub or function not defined". But what exactly ? I attached the pictures with some examples how the folder looks like also the CSV and the transfered XLS Data. And I would like my charts to have the name e.g. "DataLogger1 06.10.2017 12.00.00" but it should adapt automatically to the open file. [Folder with Files]
Thanks in advance for your help and sorry for any kind of english mistakes. English is my third language.
Copy from different workbook based on 3 criteria
I want to copy data from the workbook
15B2[...]" (sheet DATA)to the worbook. I'm starting the macro from
(sheet getDATA). The macro should copy the cells in column
DAif the cells in column
CIare blank and the column
DAhas the value 3-Incompletion in it.
Somehow the macro stops after the second if statement and goes straight to
End ifwithout copying anything:
If InStr(.Range("DA" & LastRow7).Value2, "3-Incompletion") > 0 And Trim(.Range("N" & LastRow7).Value2) = "" And Trim(.Range("CI" & LastRow7).Value2) = "" Then
I don't know exactly what this function does. Does it look in every row and counts the rows that matches the criteria?
Here is the complete code:
Sub insertINCOMPLETION() Dim dataWB As Workbook Dim reportWB As Workbook Dim workB As Workbook Dim incomplRNG As Range Dim LastRow6 As Long Dim LastRow7 As Long For Each workB In Application.Workbooks If Left(workB.Name, 4) = "15B2" Then Set dataWB = workB Exit For End If Next If Not dataWB Is Nothing Then Set reportWB = ThisWorkbook With reportWB.Sheets("getDATA") LastRow6 = .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Row End With With dataWB.Sheets("Data") LastRow7 = .Cells(.Rows.Count, "F").End(xlUp).Row If InStr(.Range("DA" & LastRow7).Value2, "3-Incompletion") > 0 And Trim(.Range("N" & LastRow7).Value2) = "" And Trim(.Range("CI" & LastRow7).Value2) = "" Then Set incomplRNG = Application.Union(.Range("F8:F" & _ LastRow7),.Range("H8:H" & LastRow7), .Range("DA8:DA" & LastRow7)) incomplRNG.Copy reportWB.Sheets("getDATA").Range("B" & LastRow6).PasteSpecial xlPasteValues End If End With End If End Sub
I need help to solve this problem because I'm not really good at programming VBA.
Run R Script from windows command line with parameters
I am trying to run an Rscript via Excel VBA. The macro will call VBShell and execute the R script.
I also want to pass a few parameters to the command line. Here is a simple version of the code:
"C:\Users\myname\Documents\R\R-3.4.2\bin\RScript "D:\Group\Subfolder path1\sub folder path 2\script.R" "D:\Group\Subfolder path1\sub folder path 2\" "raw file one.csv" "raw file two.csv" "raw file three.csv"
I ran the above code in the command windows but having issues with spacing. The error is
D:/Group/Subfolderis not regconised as an internal or external command. Does anyone know how to fix this issue? Thanks
How to delete a record using macro in Microsoft Access?
I'm trying to use this macro action:
Currently my actions (After Delete on TableB) look like this:
Look Up A Record In TableA Where Condition = Old.SomeId = TableA.SomeId DelteRecord()
I'm obviously doing something wrong. I know for sure the two values are the same, because when I replace DeleteRecord block with "Create a record in tempLog" where I set two fields to the numbers, I get them to match.
My problem is that when I want to TableA then the block should fire, lookup the value in the other table, and then delete the matching row. I just can't get this "DeleteRecord" thing to work. Am I using it wrong? How should the structure look?
Data entry form - company intranet and Microsoft access
I am hoping for some help or ideas to how I can create a data entry form in our intranet pages that is connected to Microsoft access.
Basically we have an intranet and we also have a Microsoft Sharepoint.
I would like to create a data entry form on our pulse site where everyone in the company can fill out the form. But I would like the data that is entered in the form to be saved in a Microsoft Access database in SharePoint, where only a few people have access to it. Basically the people fill in the form, click submit (i.e) then the data they inputted is saved in Access on Sharepoint for me to view.
Appreciate every help I can get or guidelines or suggestions to where I can read about this. Thank you.
VBA Exporting Access DB to Excel
I have recently built an MS Access database to hopefully streamline some paper based processes at work. I have never used Access before or done any coding in VBA so apologies in advance if my code looks awful.
The database is working as intended and now I'm working on exporting some management reports to Excel. The management team wants the data exported into a specifically formatted Excel sheet instead of an Access report.
The export is tied on the
onClickevent of the test buttton on this form
The idea is that each unique piece of equipment shown in the form subform is recorded in the cells
[C7:C14], in theory there will never be more than 8 unique pieces of equipment to record. Note: I'm not actually running any code against the subform, I have been using other tables to get the data I need using values on the form as keys.
I have been able to get the following code to perform this correctly
tagNo = """" + Tag_Number.Value + """" EqSQL = "SELECT Equipment.Equipment_Name,Equipment.Tag_No FROM Equipment WHERE (((Equipment.Tag_No)=" & tagNo & "))" EquipmentCell = 7 ResponseCell = 7 Set db = CurrentDb Set rs = db.OpenRecordset(EqSQL, dbOpenDynaset, dbSeeChanges) Do Until rs.EOF EquipmentCellSt = ("c" & EquipmentCell) With wsheet .Range(EquipmentCellSt).Value = rs![Equipment_Name] End With EquipmentCell = (EquipmentCell + 1) rs.MoveNext Loop
The next task is to export the response values, so the first piece of equipment would require all its response values to be inserted in order into
[J7:AF7]and the next piece of equipment to
[J8:AF8]and so on.
I have a table called Inspection_Detail that is updated via the subform and holds all the data I think I should need for this.
My thinking so far I need to nest another loop in my current loop and execute a SQL query that pulls the response values but for the life of me I can't figure it out.
I have written a SQL query that grabs the correct record (some values are hard codes right now)
SELECT Export_Table.Equipment_Name, Export_Table.Task_No, Export_Table.Response, Export_Table.Notes, Export_Table.Tag_No FROM Export_Table WHERE (((Export_Table.Equipment_Name)="Pipe") AND ((Export_Table.Task_No)="A.1 Equipment") AND ((Export_Table.Tag_No)="scriptTest"));
I'm not sure how to build a loop counter to iterate though
[J*:AF*]and I have no idea how to pull just the response value out of the SQL query
Apologies if I'm missing anything vital out or just flat out not making sense, I have been stuck on this all weekend and my brain is fried.
MS Access Multi-control KeyPress CTRL+A Handler
I have an Access database with 10+ text controls. I'd like to have some code to handle the CTRL + A KeyPress event. Normally, when pressing CTRL + A in Access, this selects all records. My end goal is to have CTRL + A only select that control's text (like pressing CTRL + A in your browser's URL bar, it only selects THAT text) so that I can delete only that control's text. I checked this article, as I wanted something that could handle any text box (handling each textbox's KeyPress = 60+ lines of code). Is there any way I could have, say, a for-next loop?
Function HandleKeyPress(frm As Form, KeyAscii As Integer, ByVal e As KeyPressEventArgs) 'should it be a function or a sub? For Each ctl In Me.Controls If KeyAscii = 1 Then 'I think this is CTRL + A? e.Handled = True 'Stop this keypress from doing anything in access focused_text_box.SelStart = 0 focused_text_box.SelLength = Len(focused_text_box.Text) End If Next End Function
Along with this, how can I pass to this sub/function the text box's name?
Note: In case you haven't noticed yet, I'm still a noob with VBA/Access.
Time sheet module - Gettimesheetdata subroutine
I have come a long way on this first Access project of mine but presently terribly stuck at this point for over two weeks!
This part is the timesheet module where there is a timesheetform and a timesheetsubform; on the header part of the form there is a combobox where the user picks the week ending (Friday) date. This week ending date is the main criteria for the whole timesheet. Everything till this point seems to be working fine thanks to one of Ms. Fennema’s codes which I was able to adapt.
Once the date is picked on the combobox, I must put a code in its afterupdate event (Gettimesheetdata subroutine), to first check the main table (tblTimeSheetData) if there are any workhours for the 5 weekdays of that week; (maybe through a query on the fly) and if there are, it should put them in the proper fields in the subform through the temp table so that the user can verify that that specific week's timesheet is already complete or make adjustments to it. If there are no records it would mean that this is a new timesheet! In the case that it’s a new timesheet, selecting the projects and entering the hours for the weekdays and save them to main table also seems to work (Writetimesheetdata subroutine below). As you enter the hours per day based on the project, it populates the temp table and a ‘Save the Worksheet’ command button converts the horizontal data to vertical on the main table. I would really appreciate any help to guide me in the right direction on the Gettimesheetdata subroutine.
Here is the code for the Writetimesheetdata subroutine:
Public Sub WriteTimesheetData() Set rstTime = CurrentDb.OpenRecordset("tblTimeSheetData", _ dbOpenDynaset) Set rstTemp = CurrentDb.OpenRecordset("tblTimeSheetDataTemp") With rstTemp .MoveLast .MoveFirst lngCount = .RecordCount Debug.Print lngCount & " records to write" If lngCount > 0 Then 'Attempt to find matching record in tblTimeSheetData 'Create or edit one record in tblTimeSheetData for each weekday 'that has hours worked Do While Not .EOF lngProjectsID = Nz(![ProjectsID]) lngActivityCode = Nz(![ActivityCode]) dblWorkHours = Nz(![MondayWorkHours]) If dblWorkHours > 0 Then dteWork = DateAdd("d", -4, _ GetProperty("TimesheetWeekEnding", "")) If lngActivityCode <> 0 Then strSearch = "[ProjectsID] = " & lngProjectsID _ & " And [WorkDate] = " & Chr(35) & dteWork _ & Chr(35) & " And [ActivityCode] = " _ & lngActivityCode End If Debug.Print "Search string: " & strSearch rstTime.FindFirst strSearch If rstTime.NoMatch = False Then 'Edit existing record rstTime.Edit rstTime![WorkHours] = dblWorkHours rstTime.Update Else 'Add new record rstTime.AddNew rstTime![ProjectsID] = ![ProjectsID] rstTime![WorkDate] = dteWork rstTime![ActivityCode] = ![ActivityCode] rstTime![WorkHours] = dblWorkHours rstTime.Update End If 'repeat same code for Tue, Wed, Thu and Fri End If .MoveNext Loop End If End With ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number _ & " in WriteTimesheetData procedure; " _ & "Description: " & Err.Description Resume ErrorHandlerExit End Sub
In Microsoft Access, what are the relationships between CodeData, CodeProject, and DAO..?
What are the differences between the following sets of Microsoft Access VBA collections..? Some of them seem to point to the same data & child objects, but through different conduits. I'm thinking the DAO objects are DBMS-specific, but Access.⃰Code.⃰ and Access.⃰Project.⃰ are not, and get connected through Access. There is ADODB, which is another beast entirely. Also, it seems some of the collections may not exist if there no child objects of that type.
Given the mentions of SQL features, there seem to be three scenarios to be concerned with, but it's difficult to grasp:
- A standalone ACCDB file.
- A split database with two or more ACCDB files.
- An Access frontend to an SQL server.
Object set #1:
- And of course CodeData vs. CurrentData and CodeProject vs. CurrentProject.
Object set #2:
Object set #3:
- Access.Application.CurrentDb.QueryDefs (DAO.QueryDefs)
- Access.Application.CurrentDb.Relations (DAO.Relations)
- Access.Application.CurrentDb.TableDefs (DAO.TableDefs)