Modules & VBA :: DLookup Only Reading Top Row

Jun 26, 2015

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 ??

View Replies


ADVERTISEMENT

Modules & VBA :: Reading Control Value From Public Function?

Oct 4, 2013

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.

View 4 Replies View Related

Modules & VBA :: Split Database - Reading Records

Jul 2, 2013

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.

View 6 Replies View Related

Modules & VBA :: Reading VCF Files - Fields Missing In Different Records

Mar 22, 2015

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.

View 1 Replies View Related

Modules & VBA :: Reading Web Page Source Code Data?

Feb 13, 2014

Any method of reading web page source code data.

I am trying to create an event similar to a web query in Excel.

The table in question is not a html table but an asp.net table I think.

View 3 Replies View Related

Modules & VBA :: Reading Specific Record From Linked Tables

Jul 17, 2013

I have an access 2003 database where the table is linked from mysql.

I would like to know how it is possible to read a specific record from the linked tables using vba code.

View 2 Replies View Related

Modules & VBA :: Reading A Field Control Source From For Loop

Feb 25, 2014

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

View 3 Replies View Related

Modules & VBA :: Reading Registry On Windows 7 X64 With Access 2010 X86?

Nov 22, 2013

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.

View 7 Replies View Related

Modules & VBA :: Reading Table Of Information Recordset From XLSX File

Mar 17, 2014

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

my connection string is:

Code:
strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & cstrFolder &
Chr(92) & tempSymbol & ".xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';"

I open the connection which works fine:

Code : cn.Open strConnectionString

Now here I hit the problem, I have the following SQL string:

Code : strSelect = "SELECT * from [table$]"

The spreadsheet contains just 1 tab, named table so I think I am referring to the data correctly in my SQL string?

When I open the recordet for variable rs using:

Code : rs.Open strSelect, cn, adOpenStatic, adLockOptimistic, adCmdText

I get the error dialogue box:

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.

View 2 Replies View Related

Modules & VBA :: Dlookup In A Subform?

Apr 22, 2015

I am trying to dlookup a field (field name [client]) which is on a form (form name bkpo)

The code i use is this

CLNM = DLookup("[ClientName]", "[BKPOCL]", "[ClCode] ='" & [Forms]![BKPO]![Client] & "'")

It works just perfect however if I use the form BKPO as a subform then the code doesn't work

The form is BK1b

and the subform is BKPO

I tryed this but doesn't work

CLNM = DLookup("[ClientName]", "[BKPOCL]", "[ClCode] ='" & [Forms]![BK1b].[BKPO]![Client] & "'")

I have tryed several other options like [Forms]![BK1b]!BKPO.[Client] or [Forms].[BK1b]![BKPO]![Client] but nothing

View 7 Replies View Related

Modules & VBA :: LIKE In DLookup After Syntax

Nov 5, 2013

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.

View 5 Replies View Related

Modules & VBA :: DLookUp With 2 Criteria

Aug 4, 2015

I have a form that has a field that needs to get its data from a query based on two fields in the form. The following is my DLookUp statement:

DLookup("[COMEX]", "OptionMetalsListQ", "[Metals] = '" & [cboMetals].[Column](1) & "'" And "[DateOfPrice] = " & Me.txtDateOfPrice)

Comex is a metal market. the bound column in cboMetals is text and The date is a date.I am getting the error "Run-time error '13': Type Mismatch.

View 3 Replies View Related

Modules & VBA :: Nested DLookup In SQL UPDATE

Jul 31, 2013

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:
SQLstock1 = "UPDATE TBL_STOCK SET Stock = '" & DLookup("RemainingQty", "QRY_STOCK", "ActionEntity = '" & Me.cmb_customer1 & "'" And "StockType = '" & Me.cmb_stock_type1 & "'") & "' WHERE StockEntity = '" & Me.cmb_customer1 & "'"

View 1 Replies View Related

Modules & VBA :: Str Variable In DLookup Criteria

Aug 18, 2013

Why does this work when the text box is used and not when the variable is used directly?

Code:

Private Sub Command61_Click()
Dim strDBName As String
strDBName = getDBName()
Me.Text59 = strDBName
Me.Text62 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = text59")
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = " & strDBName)
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName] = strDBName")
'Me.Text64 = DLookup("[ModuleName]", "tblModule", "[DatabaseName]" = strDBName)
End Sub

Text62 returns the correct value
Text64 failes on everyone of the examples

View 7 Replies View Related

Modules & VBA :: DLookup Not Returning Any Results

Mar 26, 2014

Here is my VBA Dlookup Code:

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.

View 4 Replies View Related

Modules & VBA :: Getting Values From A Table Via DLookup

Aug 15, 2013

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

Which gives me an update clause error

View 4 Replies View Related

Modules & VBA :: Syntax With Filter In DLookup

Feb 26, 2014

If I type 2014 instead of b everything works out.

Code:

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

View 4 Replies View Related

Modules & VBA :: DLookup With Date In Form

Mar 5, 2014

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:

Code:
=DLookUp("[Total Input]","DailyDataQry","[Work Date] = #" & [Forms]![InputFrm]![WorkDateAtxt] & "#")

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?

View 3 Replies View Related

Modules & VBA :: Dlookup Resulting In Null Value

Aug 15, 2015

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?

View 5 Replies View Related

Modules & VBA :: DLookup In ODBC Table

Feb 15, 2015

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.

View 4 Replies View Related

Modules & VBA :: Converting SQL Statement To DLookup

Jun 27, 2013

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

Code:

= DLookup("[i_closing]", "tbl_p_stats", "[i_weekending] = " & i_olddate & " AND [i_sacat] = " & wfid1 & " AND [i_complexity] = " & wfc1)

However, it is showing the control (wf1oi) as a blank control now. When I run the dlookup through the immediate window, the returned result is Null.

A little more detail
- All the mentioned controls live on the same form (frm_a)
- The control that this Dlookup is going into is called wf1oi

View 10 Replies View Related

Modules & VBA :: DLookup In INSERT Statement

Jul 10, 2013

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:

Code:
Dim lookModType1 As Variant
lookModType1 = DLookup("module_type", "tbl_module_repairs", "prikey = " & Me.txt_prikey1.Value & "")
SQLtext4 = "INSERT INTO TBL_RF_TECH_LOG ([TechID], ActionStatus, Barcode, EquipmentDescription) Values (Left([txt_techid_rework_in], 2),'In Rework', '" & Me.txt_bc1 & "', " & lookModType1 & ");"

DoCmd.RunSQL SQLtext4

Note: My DLookup works fine by itself for other uses.

View 5 Replies View Related

Modules & VBA :: DLookup To Excel Range

Jul 17, 2014

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 .....

LOOK = DLookup("F6", "DATABASE_LOOKUP", "F1 = 1338087")

F6 = A date I am looking up.
DATABASE_LOOKUP = table I am looking up.
F1 = 1338087 = The record I am looking for.

View 5 Replies View Related

Modules & VBA :: Dlookup With Multiple Condition

Apr 8, 2015

I Have one main form in access 2003 i want to use Dlookup function to 1 text box with 2 criteria is it possible?

My Formula:

Text67. Value = DLookup("Balance", "dbo_WbookEdit", "[Lotno] = '" & Forms![Edit]![LotNo] & "'" And " & [Description] = '" & Forms![Edit]![Description] & "'")

View 6 Replies View Related

Modules & VBA :: DLookup Compile Error

Jun 23, 2014

why I am getting "Compile Error: Expected: =" here:

Code:
Private Sub Form_Load()
MeText9 = DLookup("[Along_line_spacing]", "Performance_Requirements_Defaults_Table")

when this:

Code:
Private Sub Form_Load()
Me.charttypestatic = DLookup("[Static_Chart_Type]", "Project_Defaults")

works??The "Performance_Requirements_Defaults_Table" table is linked, the "Project_Defaults" table is not.

View 7 Replies View Related

Modules & VBA :: DLookup Missing First Record In Table

Sep 23, 2014

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.

Here is the code I am using:-

LastIDRef = 0
strToEmail = Null
strMailToAddress = Null
AdminCount = DCount("[ID]", "[LogIn]", "[LogIn].[Security Level] = 'Admin'")
Do
strLinkCriteria = "[LogIn].[Security Level] = 'Admin' And [LogIn].[ID] > " & LastIDRef

[Code] ....

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved