I have a module which reads a CSV transaction file line by line and adds the correct transactions to an access table and places the wrong ones in a logfile.Now some transactions are rejected twice there is even one rejected six times. Whereas one wrong transaction is processed only once. I am certainly overlooking something obvious in the logic but what. Here is the relevant code.
Code:
Function ImportCSVForConfederation(inputCSV, ORG) Dim TNO As Integer, TACT As Integer, TABLE As String, TLINE As String, I As Integer, J As Integer, K As Integer Dim FLD1 As String, FLD2 As String, FLD3 As String, FLD4 As String, LogFile As String, LogPath As String Dim Lim As String, ITNO As Integer
what is the correct syntax to write a command line able to UPDATE more than one field in the table records having multiple WHERE criteria.
Here is my challenge:My TableI has the columns A, B, C and D which are populated, for example, as follows:
TableI A B C D 1 2 2 6 4 3
1 7 5 9 1 2 2 5 8 5 etc.
I also have a FormII which updates TableII. Among the existing fields of TableII there are the fields C and D (same as above). When saving data entry thru the save button of the FormII, fields C and D will be naturally saved on the TableII. Well, I also want C and D info updated into Table I as well, but only when field A=1 and B=2.So what I need (for the click event of the button save in the FormII) is to open TableI and either insert or update it with the values of the fields C and D in every record WHERE A=1 AND B=2.For instance, assuming C=& and D=%, the desired result should be as follows:
TableI A B C D 1 2 & % 2 6 4 3 1 7 5 9 1 2 & % 2 5 8 5
I did not find any examples in the net including multiple criteria..Here is what I wrote unsuccesfully:
Private Sub BtSalvarFrmII_Click() CurrentDb.execute "UPDATE TableI" Set FieldC = Forms!FrmII!FieldC.value AND Set FieldD = Forms!FrmII!FieldD.value WHERE FieldA = 1 AND FieldB = 2 Docmd.save Docmd.close End Sub
I have a program.mdb with a customized menu bar from Database Creations.When I open program.mdb in 2007 & 2010 the ribbon is disabled and the custom bar is displayed as it should be and all is OK.When I open the same program.mdb in 2013 the ribbon is displayed and the custom bar is missing
I have a clean compile and get no error messages.How do I disable the ribbon and get the custom EZ Menu Bar to display in 2013?.Following are the Options, Current Database, Ribbon and Tool Bar Option settings that is used in all Access 2007, 2010 & 2013 versions
Ribbon Name: Menu Bar:EZ Menu Bar Shortcut Menu Bar:(default) CheckedAllow Full Menus CheckedAllow Default Shortcut Menus UncheckedAllow Built-in Toolbars
The following code works fine until it gets to last record, then it give me Error # 94 Invalid use of null. I have searched all of my code and null is not in the code.
Code: '-------------------------------------------------------- ' Goes to next record '--------------------------------------------------------- Private Sub cmdNext_Click() On Error GoTo err_handler ' DoCmd.GoToControl (txtNameL) ' DoCmd.GoToRecord , , acNext
I am using VBA in Access 2013 to make a PowerPoint presentation using data from the access database. know if it is possible to use Access VBA to create a button in the presentation that runs a procedure itself?
I need to save a single record (preferably the current record) as a PDF. I have created a form for my boss to calculate bids. He wants to save each record individually as a PDF to upload into our service software program to attach to client files. I can save a selected record as a PDF if I print the selected record first. We need to eliminate having to print it first as it is just a waste of paper and an unnecessary step. I am kind of thinking that VBA and attaching that to a control button on the form is the way to go, but I am new to writing code and I am completely confused!
I need to make a copy of a pdf document that the user identifies in a "hidden" folder that is associated with my backend database. how to code this. I am using Access 2013.
I have Access 2013, Win 7, Outlook 2010 (32-bit).I want to send an email from Access, but not through Outlook. Instead I want to connect to an external SMTP server. I've found a few examples that doesn't work for me. With code including for example
Code: Set ObjMessage = CreateObject("CDO.Message")
I get an "Error 429: Object can't be created in Active X-component" (My translation from Swedish).With code including this:
Code: Dim oEMail As New CDONTS.EMail
I get "user defined type has not been defined" (my translation again).I have working code for sending email through Outlook, which works fine. But, Outlook adds a lot of span tags, making it impossible to read for certain screen readers used by visually impaired people, therefore I can't use Outlook.
I've tried to read up on the matter (CDO), and think that maybe the CDO library etc is not included on my computer?can I download it?
The columns are PayPeriod, StartDate, EndDate, FiscalYear
What I want to be able to do is highlight a chunk of dates and have the first selected StartDate and the last selected EndDate populate two hidden text boxes so I can use them for my queries/reports.
I've tried a couple different ways. Each time what happens is it only uses the last item I have selected in it's calculations.
Code: Dim ItemIndex As Variant For Each ItemIndex In Me.lstPayPeriods.ItemsSelected If Me.lstPayPeriods.Selected(ItemIndex) And Me.lstPayPeriods.Selected(ItemIndex - 1) = False Then Date1.SetFocus Date1.Text = Me.lstPayPeriods.Column(2, Me.lstPayPeriods.ListIndex) End If Next
In this example I tried to have it go through each Item of the listbox. I wanted to check to see if the current row was selected and the row before it wasn't. That way I could determine it was the first item selected in the group of selected items. It would always only use the last item I had selected.
Code: Dim CurrentRow As Integer Dim FirstDate As Date For CurrentRow = 0 To Me.lstPayPeriods.ListCount - 1 If Me.lstPayPeriods.Selected(CurrentRow) Then Date2.SetFocus
[Code] ....
I tried to do something similar with this code. Again, it only uses the last item I have selected.
I'm about to connect my DB to word. So I made a form where the user has to choose what entry to export. The data is inserted in a word file in different bookmarks. But there is one special task to insert the rich text so in this case the html formatted text is displayed like this:
I am trying to improve my code by making it more readible. The following code works, but it is certainly not the most efficient way. I`m trying to write a loop to make certain elements in an Access report visible/invisible, but I can`t address the visibility property of these items while iterating over i.
I am having a little difficulty with my importing in Access. Every time I import my text file, the lines will be jumbled. I have been reading up and I found this recordset code that seems to be what I need:
Code: Dim strLine As String Dim intLineNum As Integer Dim MyDB As DAO.Database Dim rst As DAO.Recordset Open "C:TestTest.txt" For Input As #1 Set MyDB = CurrentDb Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset) CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError 'Clear tblResults
[code]....
Basically, this code will extract data from the text file as long as it fulfills the Mid$ criteria. Here's where my problem comes. Each line in my text file is of different lengths and I have to capture the entire line.
I think using the Left$ function would work, but I don't know how to determine the character count such that the entire line of text would be inserted into the table.
Another difference between what I need and the code above is that, I am required to store each line into each row of my table, meaning
Line 1 is placed in Row 1 Column 1 Line 2 is placed in Row 2 Column 1 Line 3 is placed in Row 3 Column 1 . . . Line X is placed in Row X Column 1.
I am trying to Hide or Show the Access Window by using two buttons on a Form. Running Access 2013 in Windows 7 64bit.
I have used code from the Internet as below but added the PtrSafe item to the Function Declaration as below:-
Option Compare Database 'Hide Access desktop screen and float Forms on pc desktop Private Declare PtrSafe Function IsWindowVisible Lib "user32" (ByVal hwnd As Long) As Long
Dim dwReturn As Long Const SW_HIDE = 0
[Code] ....
I then have two buttons on a form coded as below:-
Option Compare Database Private Sub cmd_Hide_dbw_Click() Call fSetAccessWindow("Minimize", False, False) DoCmd.OpenForm "frm_Test", acNormal End Sub
[Code] .....
I have attached a Test Database which demonstrates the problem I am experiencing.
Do the following sequence...............
Open the Test Database. DO NOT ENABLE CONTENT The Access 'Ribbon'Test_Database.accdb is active and usable Open frm_Test in Design View in order to inspect, all should be as shown above. Now click the Enable Content warning Switch frm_Test to Form View
Click button Hide dbw. Closes the Access Window Click button Show dbw. Opens the Access Window...HOWEVER....
Attachment 20252The Access Ribbon is locked and usable !!!
I have a form I use to search for clients when I hit a command button, using surname etc. I'd like a yes no button to pop up rather than the clunky ok button route I currently have. I'm a beginner with VBA and most this I've gleaned from internet.
If there are matching results, everything is fine. If there are no matching results, I've managed to get my desired messagebox come up by using the code below in the onload event of the resulting search results form, however the yes no buttons do nothing and the results form loads blank as though no records were found.
I'd like Yes to send users to the client input form, and No to return them to the search form.
Private Sub Form_Open(Cancel As Integer) If Me.Recordset.RecordCount = 0 Then MsgBox "Do you want to add a new Client?", vbQuestion + vbYesNo, "No clients found" Select Case intanswer Case vbYes
I'm using this stardard piece of code on a button that deletes a record from a simple continuous contact form.
Code: If MsgBox("Do You Want To Delete This Record?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdDeleteRecord End If
If the form is called "CONTACTS" with a field called "Contact_Name", what code do I use to have the relevant Contact_Name appear in the MsgBox to be sure that I'm deleting the correct record ...... something like this .....
Do You Want To Delete The Record Called Paul Quinn ?
I have created a database that is hosted on our company sharepoint. The database contains subcontracts with resource information by geographic location and status. I have built queries and added them as reports to the web app but I need to figure out a way to sum totals on the queries. For example, one report lists all resources of a particular type by 4 different zones and 3 different status. I need to have the bottom row show the total resources.
I have problem sharing an Access 2013 file from one cumputer and then change the data in the file on another computer, or i can change it but the change wont be made one both computers.
I'm working so i need my secretary to be able to change information or add, but i also need to get the change.
I currently use this code to trigger a not in list event, ask the user if they want to add it to the respective table, and then add it.
I'd like to be able to add the text in the combo box that triggers the event to show up in the msgbox.
Private Sub cboCategory_NotInList(NewData As String, Response As Integer) Const Message1 = "The data you have entered " & me.cbocategory.text &" is not in the current dataset." Const Message2 = "Add now?" Const Title = "Unknown entry in CATEGORY Field..." Const NL = vbCrLf & vbCrLf
I have a database in which users have the ability to search by last name...nothing fancy there. However, the users continue to forget to click the Find Client button and instead just type what name they are looking for in the LASTNAME control thus changing the record that they are currently in!!!
I am looking for a way to have a msgbox pop up any time the LASTNAME control is changed. I found the code below but this works for the entire record and I don't want them to have to click Yes every time they make change.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer) If MsgBox("Changes have been made to this record." _ & vbCrLf & vbCrLf & "Do you want to save these changes?" _ , vbYesNo, "Changes Made...") = vbYes Then DoCmd.Save Else DoCmd.RunCommand acCmdUndo End If End Sub