I have a command button on a Access 2010 form that i am using as my switchboard. On this form i have a hidden unbound text box that captures the users environ"username". When the user hits a command button on the form the code looks at the name in the hidden textbox that captures the environ"username" and then DLooks up a table to see if there is a match. If yes then it will open the next form and if not then a message box appears.
Code:
Private Sub Command6_Click()
Dim TxtUsername As String
If Me.TxtUsername = DLookup("[OneLondon Login]", "TblAccessUsers") Then
DoCmd.OpenForm "Bakerloo_Main_Form"
Exit Sub
Else
MsgBox "You do not have permission to access this database"
End If
End Sub
This works fine apart from the fact it will only read the first name entered in the table. This table(TblAccessUsers) could have up to 50 names in it and possibly have names removed and re added at a later date. Is there a way i can get the code to look up every name in the table ??
I am trying to read the value of a control on an open form from a Public Function in a Module with no luck. I suspect the issue is syntax.
Code:
Public Function MyFunction(varFormName As String, varControlName As String) If Forms(varFormName).Controls(varControlName) = True Then ' = 0 even when control is -1 Else End If End Function
The variables pass through fine but the If statement equals zero regardless of the state of the form's control. I tried several variations to no avail.
If Forms(varFormName).Form.Controls(varContactControl ) = True Then If Forms(varFormName)!Form.Controls(varContactControl ) = True Then Etc.
My database was working fine until I split the database and now I'm having issues with this function:
Code: Public Function OpenFirstAttachmentAsTempFile(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String) As String Dim rstChild As DAO.Recordset2 Dim fldAttach As DAO.Field2 Dim strFilePath As String Dim strTempDir As String
[Code] ....
It's getting stuck on the "strFilePath = strTempDir & rstChild.Fields("FileName").Value" line. The debugger says that there is no reord. The strTempDir is correct. I am assuming that the issue has to with linking to the database.
I have an application where I have to read a big VCF file in VBA, extract the information and place it into records of a table. Of course I can read as a text file, but the structure is quite cumbersome to implement with many fields missing in different records and it is not a one time job.
I want to create a table of all fields in a form, their datatype and their controlsource.
I have done the first 2 parts easily enough using the forms.formname.controls.name property and TypeName(forms.formname.controls)
But I am unable to pragmatically read the controlSource. EG forms.formname.controls.txtname.controlsource works
but when In a for loop
I cannot use the name of the control to return the control source.
Code: Sub PopTab Set frm = forms.FrmCases for each CTl in frm.controls MyName = ctl.name MyType = TypeName(ctl) MySource = ctl.name.controlsource end if next ctl end sub
I have always had solid read access to the Windows registry running on Windows XP with Access 2007. I am utilizing code from here in a separate VBA Module to obtain access to the Windows registry:
"Change registry settings in VBA"
[URL]
On Windows 7 x64 / Access 2010 x86, I am seeing a 0 value in lngKeyHandle. Also, m_lngRetVal has a value of 2 after the RegOpenKey LOC.
Code: ' -------------------------------------------------------------- ' Query the key path ' -------------------------------------------------------------- m_lngRetVal = RegOpenKey(lngRootKey, strRegKeyPath, lngKeyHandle) ' -------------------------------------------------------------- ' If no key handle was found then there is no key. Leave here. ' -------------------------------------------------------------- If lngKeyHandle = 0 Then regQuery_A_Key = vbNullString m_lngRetVal = RegCloseKey(lngKeyHandle) ' always close the handle Exit Function End If
I have verified that I have the key successfully in the registry... installed via the .reg file that works on Windows XP / Access 2007 systems.
I am trying to use a Connection to an xlsx spredsheet to read in a table of information (the data is not a table, just laid out in a table, see attached, the one I am trying to load is xlsx not xls but I cannot for some reson upload the xlsx one on here)
I have the following declared:
Code: Dim cn As ADODB.Connection Dim rs As ADODB.Recordset
The Microsoft Access database engine could not find the object 'table$'. make sure the object exists and that you spell its name and the path name correctly.....
I did a Debug.print to check the strConnectionString was correct and the Source is exactly correct and refers to the name of the file I am trying to access exactly.
I am looking to use a "If" statement with DLookup to find a record and see if the field name begins with "TW" as the characters. Here is what I have so far:
Code:
Dim matchCriteria As String matchCriteria = "LIKE 'TW*'" If DLookup("end_user", "tbl_module_repairs", "prikey = " & Me.txt_rid1.Value) = matchCriteria Then MsgBox "Success" Else MsgBox "FAILURE" End If
I am trying to switch between two sets of pricing for customers that begin with "TW" and then all else. Would the use of a "case" statement be better? If so I do not have the "end_user" bound to the form so I would need to embed Dlookup in the "case" method.
I have a nested DLookup in a SQL UPDATE that is not working. The DLookup has an "AND" in the WHERE Statement as well. I cannot figure out the problem. I got a type mismatch with this code and when I take out the single quotes I do not get an error message but the data is not updated in the table.
Code: Public Sub test() Dim frm As Form Set frm = Forms!StationLevelSummary
[code]...
At the bottom im printing the content of the controls which are on my form. These should return 1 number, but for some reason it does not. Ive used this code many times but I cant figure out why nothing is being returned.
I'm pretty familiar with getting values from a table via Dlookup. What I want to do is almost the reverse if possible? I'm declaring a variable as follows:
Dim Ref as string Ref = [lead_id]
This is from a form.What I'd like to be able to do is go to the table [list], reference the lead ID in the table via the variable then change the field [status] to "INCALL".Can this be done in a similar way to Dlookup?
UPDATE - here is the code I am trying to use
Dim ref As String ref = [lead_id] Dim MySQL As String MySQL = "UPDATE vicidial_list SET" MySQL = MySQL & "vicidial_list.status = 'INCALL' " MySQL = MySQL & "WHERE (((vicidial_list.status)= Ref))" DoCmd.RunSQL MySQL
Dim a As Double Dim b As String b = Right(RepMonth, 4) a = DLookup("[EUR/TRY]", "Plan_FX_RATES", "YEAR = '" & b & "'") Debug.Print a Debug.Print b Debug.Print RepMonth
At the moment I have a form (InputFrm) for operators to enter production processes into the system. Each one of these has a certain weight which the operators enter and it all stores in InputTbl, which works great.
However I am trying to have a running total of the kilograms inputted on each day displaying when a new record is opened. To do this I have created a query (DailyDataQry) which provides me a running sum of how much input has gone through the process, and I can clearly see the data in this query.
I am attempting to have a text box in the upper right of my form which displays this number as after a certain weight of input the operators are needed to perform checks.
This is the code I have at the moment in the control source of a text box (DailyInputTotalTxt), but it is just returning a blank:
The work date is generated automatically when the new record is created by having Date() in the default value of the work date (WorkDateAtxt) on the form.
Is there any way which I can use this value to lookup the current total of input contained within DailyDataQry?
I am trying to run this code but getting a run time error 94 - null value - when it shouldn't be.I am thinking I have written the Dlookup incorrectly?
Code: Private Sub txtProductName_Click() Dim iProdType As Integer Dim ProductID As Integer iProdType = DLookup("ProductTypeID", "tblProduct", "ProductID" = Forms![frmBooking]![cboProductID].[Value]) Note ProductID in an integer
How can I diagnose this or what could the solution be?
I set up an Access 2003 database where this code works OK:
Private Sub Street_Exit(Cancel As Integer)
Me.Suburb = DLookup("Suburb", "Streets", "StreetName = Forms![Add A New Member]!Street") Me.StreetID = DLookup("ID", "Streets", "StreetName = Forms![Add A New Member]!Street") Me.Postcode = DLookup("Postcode", "Streets", "StreetName = Forms![Add A New Member]!Street") Me.Town = DLookup("Town", "Streets", "StreetName = Forms![Add A New Member]!Street")
End Sub
I've now migrated the tables to a back-end in Azure using ODBC to connect. The linked Streets table in my list displays as dbo_Streets but when I change the table name in my code I get an error.
I have the following SQL Statement which returns the desired result to me
Code:
SELECT tbl_p_stats.i_closing FROM tbl_p_stats WHERE (((tbl_p_stats.i_weekending)=[forms]![frm_a]![i_olddate]) AND ((tbl_p_stats.i_sacat)=[forms]![frm_a]![wfid1]) AND ((tbl_p_stats.i_complexity)=[forms]![frm_a]![wfc1]));
And am now trying to convert it to a Dlookup to provide the default value to a control on form Loading. The statement i came up with is
I need my INSERT statement to DLookup tbl_module_repairs and insert the information from a field based on a WHERE condition of primary key matching on the form and table. Here is what I have but it will not work on the last value:
I am having problems with Dlookup from a table which is linked to Excel.The heading on the fields from Excel have been imported as F1, F2, F3 etc... the code I am using is as follows .....
I am having an issue with DLookup, which, for some reason which I do not understand appears to missing the first record in the table.
What I am trying to do is to extract the e-mail address of all users who have 'Admin' status to the database. In the table ('LogIn') there are 3 users who have 'Admin' status and this is confirmed by the DCount function in the following code. There respective ID values in the table are 1, 49 and 51.
Yet when I run the Loop to get all e-mail addresses it misses ID 1 and just returns records for ID 49 and 51.
I have tried changing other users status to 'Admin' and it finds them but always misses the first record.
