problem with dlookup in access 2007 Results


Page 1 of 2.
Results 1...20 of 36

Sponsored Links:



Good morning,

I have a database that uses a function of the Dllokup to find files in a network directory and display them in a small inset window. I then refine from there if the project number there already to cycle a revision number to the name of the export file I am about to export into that network location. The problem is that in Access 2007 this function of Dlookup results an error and says it isn't supported in this version, it works fine in 2003. What should I do to get the same result?

Any help understanding this issue will be greatly appreciated.
Brian

Here is an example of the code that pulls in the information:

Private Sub Form_Open(Cancel As Integer)
'Create a list of existing text files
Dim fs As Variant
Dim i As Long
Dim adocmd As New ADODB.Command
Dim adors As New ADODB.Recordset

Set adors.ActiveConnection = CurrentProject.Connection
Set adocmd.ActiveConnection = CurrentProject.Connection

adocmd.CommandText = "delete from tblSort"
adocmd.Execute

lstDrawing.RowSource = ""
Set fs = Application.FileSearch
With fs
.lookin = txtPath
.SearchSubFolders = True
.fileName = txtFileName
If .Execute() > 0 Then
On Error Resume Next
For i = 1 To .FoundFiles.Count
adocmd.CommandText = "insert into tblSort values ('" & .FoundFiles(i) & "')"
adocmd.Execute
If err.Number 0 Then
MsgBox "duplicate drawing numbers in search path - " & .FoundFiles(i)
err.Clear
End If
Next i
Else

'Dim answer As Integer
' answer = MsgBox("There were no existing material requsition files found.", vbInformation, "Existing Material Files")
End If
End With
On Error GoTo 0
Set fs = Nothing

Dim strText As String
strText = "select filename from tblsort"
adors.Open strText
While Not adors.EOF
lstDrawing.AddItem (Replace(adors("filename"), txtPath & "", ""))
adors.MoveNext
Wend
'adors.Close

'Bump up new text file revision number for next issue
Set fs = Application.FileSearch
With fs
.lookin = txtPath
.SearchSubFolders = True
.fileName = txtFileName
If .Execute() > 0 Then
On Error Resume Next
Dim REV As String
REV = txtRevNo + 1
If REV < 10 Then
txtNextRev = "0" & CStr(REV)
txtTrueNextRev = Left(DLookup("[IssueName]", "qrytblSort"), 6) & [PROJ] & "_" & txtNextRev & ".txt"
Else
txtNextRev = REV
txtTrueNextRev = Left(DLookup("[IssueName]", "qrytblSort"), 6) & [PROJ] & "_" & REV & ".txt"
End If
Else
txtNextRev = "00"
End If
End With
adors.Close
End Sub




Whats wrong with the following syntax:-
=DLookup("[Billing Rate]", "Employees", "[ID] = ""cboEmployee.(column(2)")

which is the Control Source for a field on my current sub-form.

I am trying to lookup the value of 'Billing Rate' in 'Employees'(Table) where 'ID" = the current entry in column 2 of my Combo Box ?????




Hi,
I am new to access and I have a problem.I have a very simple database with one date field.I need a pop-up or a reminder two months prior to the date date in that date field.
I've tried using macro with the following condition(Done it in Access 2007):

DLookUp("[Deal End Date]","contacts")=DateAdd('m',2,Date())

with the following actions: msgbox , openform

It seemed fine for the first record but doesnt work with any other records.I cant get it to parse through all the records.

I've tried DBA (am new to this too and it doesnt seem to work).Here is the code:


Function reminder_Reminder()


i = DLookUp("[ID]", "contacts")
x:
If (DLookUp("[Deal End Date]", "contacts", i) = DateAdd("m", 2, Date)) Then
Beep
MsgBox "Deal End Date in 2 Months", vbOKOnly, "Reminder"
DoCmd.OpenForm "contacts", acNormal, "", "DLookUp(""[Deal End Date]"",""contacts"")=DateAdd('m',2,Date())", acEdit, acNormal
i = i + 1
End If
If i = Null Then GoTo k Else
GoTo x
k:
End If


reminder_Reminder_Exit:
Exit Function

reminder_Reminder_Err:
MsgBox Error$
Resume reminder_Reminder_Exit


End Function



This doesnt seem to work either...i guess the whole is going into a infinite loop as my system doesnt respond for a loooon time.

I very much appreciate any of your input and will be grateful for any help.


Thank You,
Priya




Hi all
Can anyone help me with this code. I am having a form using a query to list my coustomer to be sent email who should be on time for apointment.
In Access 2000 I used While-Wend and it worked but in access 2007 it didn“t work so I change to Do While - Loop
Here is the code in Access 2000:
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.MoveFirst
DoCmd.GoToRecord , , acFirst
While (Not (rst.EOF))

If IsNull(Me.FRTE) And Not IsNull(Me.txtEmail) Then
Dim strStDocName As String

strStDocName = "FBolusetning"
DoCmd.SendObject acReport, strStDocName, acFormatRTF, txtEmail, , , "Minnum į bólusetningu, sjį višhengi", "Skilaboš frį " & " " & DLookup("Stofa", "Notandi"), False
End If

rst.MoveNext
If Not rst.EOF Then
DoCmd.GoToRecord , , acNext

End If
Wend

So I tryed to change it in Access 2007 to:
Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb

Set rst = Me.RecordsetClone
rst.MoveFirst
DoCmd.GoToRecord , , acFirst

Do While Not rst.EOF

If IsNull(Me.FRTE) And Not IsNull(Me.txtEmail) Then
Dim strStDocName As String

strStDocName = "FBolusetning"
DoCmd.SendObject acReport, strStDocName, acFormatRTF, txtEmail, , , "Minnum į bólusetningu, sjį višhengi", "Skilaboš frį " & " " & DLookup("Stofa", "Notandi"), False
End If

rst.MoveNext
If (Not (rst.EOF)) Then
DoCmd.GoToRecord , , acNext
End If

Loop
If I didn“t use DoCmd.GoToRecord , , acNext then it didn“t go to next Record but now it give the error on the last record "Can“t go to the specific record"
So how can I get it to go through all my records to send without giving a notice and stoop after the last record? What am I doing wrong?

Gauja




I'm wondering if someone can give me insight to a problem that I have. I have 3 db called AOMSChart.accdb, AOMSSurgery.accdb and AOMSLetters.accdb. I've run the following code from AOMSChart.accdb on Win XP and Win Vista systems without problems in both full Access 2007 and Runtime versions.


	Code:
	Public Function Launch(FN As String, PTFile As String)
' FN = File Name
' PTFILE = Path to File
On error goto Err_Launch

Launch = ShellExecute(0, "open", FN, "", PTFile, 1)

LaunchExit:
Exit Function

Err_Launch:
Msgbox Err.Number & ": " & Err.Description
Resume LaunchExit

End Function

I will use the above code to launch various other Access DBs such as:


	Code:
	Private Sub cmdSurg_Click()
'this launches AOMSSurgery.accdb
'FN = File Name
' PTFILE = Path to File
Dim FN As String
Dim PTFile As String

FN = DLookup("AOMSSurgery", "CHANLinkPaths")   '  
PTFile = ""

Call Launch(FN, PTFile)		'Public

End Sub

The above code works fine on all windows systems including Win 7. However the following code works in Ac2007 but not in runtime on any of my Win 7 workstations. I can launch the AOMSLetters.accdb from Explore and from the cmd, but not form this code.


	Code:
	Private Sub cmdLtr_Click()
'this launches AOMSLetters.accdb
'FN = File Name
' PTFILE = Path to File

Dim FN As String
Dim PTFile As String

FN = DLookup("AOMSLtr", "CHANLinkPaths")   
PTFile = ""

Call Launch(FN, PTFile)		'Public

End Sub

I'm baffled and would appreciate any input to my problem. Both the FE dbs are keep in the mydocuments folder on the workstations. I'm not even sure its even related to Win7 at all. When I click the cmdLtr button nothing happens and no errors.




Hey guys,
I can get this 1st script to auto-generate an email (outlook 2010), and I can get the 2nd script to open an unaddressed email with my report in the body, but I can't get the exporthtml script integrated into the MissingInfoRouteMessage script. Any tips?
The script I have for the Access 2007 generated email is:
Private Sub MissingInfoRouteMessage()
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim str_SQL As String
Dim str_SQL1 As String
Dim varX As Variant
Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

Export = exporthtml '**(this being my pathetic attempt at merging, see next script)
varX = DLookup("[E-Mail]", "t_Routing", "[Mfg_Cd] is not null")
With objOutlookMsg

'Set objOutlookAttach = .Attachments.Add(file path)

Set objOutlookRecip = .Recipients.Add(varX)
objOutlookRecip.Type = olTo
Set objOutlookRecip = .Recipients.Add("")
objOutlookRecip.Type = olCC
.Subject = "International Authorization"
.HTMLBody = "Hi Team,
Please let me know if the following orders are okay to approve." & vbCrLf & vbCrLf & Export
.Importance = olImportanceHigh

For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

And this HTML script that converts a report to text to put in the body of an email:
Function exporthtml()
Dim strline, strHTML
Dim OL As Outlook.Application
Dim MyItem As Outlook.MailItem
Set OL = New Outlook.Application
Set MyItem = Outlook.Application.CreateItem(olMailItem)

DoCmd.OutputTo acOutputReport, "Report-q_Workflow", acFormatHTML, "file path"

Open "file path" For Input As 1
Do While Not EOF(1)
Input #1, strline
strHTML = strHTML & strline
Loop
Close 1
' If OL2002 set the BodyFormat
If Left(OL.Version, 2) = "10" Then
MyItem.BodyFormat = olFormatHTML
End If
MyItem.HTMLBody = strHTML
MyItem.Display
End Function

The next issue I'm having is that I need the email to send only specific parts of the report to specific email addresses. I.E. 6 tuples might get sent to one address, 4 tuples to a 2nd address, and so on. But I'd rather get this initial problem solved first (I welcome tips with this though).
Thanks! Let me know if I didn't properly explain the background.




Hi all,
I had this problem and I thought I might share the solution with you:
I use access 2003 in development and distribute with Access 2007 run-time.

I have a form with a combo-box, two buttons ('delete' and 'close') and a sub-form, that the information inside it is linked to the combo-box.
The table that the form is linked to contains a field for 'soft deletion' called 'ac_deleted'. Obviously I would like to enavle the cmd_delete control if and only if the ac_deleted field is marked.

In access 2003 this line would work perfectly:
Me!cmd_delete.Enabled = Me!sub_ac.Form!ac_deleted

But in run-time 2007 it would give me a 'type mismatch' error whenever I changed the data in the combo box (=changed the data that the sub form is linked to).
So I changed the line into this:
Me!cmd_delete.Enabled = IIf(Nz(Me!sub_ac.Form!ac_deleted, 0) 0, True, False)

But in run-time environment it 'sometimes worked'. i.e. sometimes it would display the data properly (or in my case, disable the control) and sometimes it wouldn't.
My work around for this problem was simply to use the 'DLookUp' function (this table contain less then 1000 lines) like this:

Me!cmd_delete.Enabled = IIf((DLookup("ac_deleted", "dbo_account", "ac_id =" & Me!cmb_account) 0), True, False)

(please note the the 'ac_deleted' field cannot be NULL)
And that gave me (finally) the consistent results which I was looking for.
I thought I'd post this in case anyone else has this problem.




Hi,

I have tried to do this before but had to go a really complicated route round to get what I wanted... but I was hoping access 2007 had a nicer solution to my problem...!

Basically, I have a continuous form with 20 or so fiends in each record. (It looks like a register)
What i need to be able to do is highlight every field (in each record individually) which is different from a certain field in a different table (i.e. I make a copy of the source table weekly & I need to higlight the changes that have happened since that copy was taken)

I have the following very simple bit of code, which highlights fine, but it highlights EVERY record, even if only the 1st one is different:

Dim strCriteria As String

strCriteria = "[ProjectNo] = " & Me.Project & " and [Rev] = " & Me.Rev

If Me.Name= DLookup("[Name]", "TblLastVersion", strCriteria) Then
Else
Me.Name.BackColor = RGB(204, 238, 230)
End If


Any ideas if 2007 solved this problem?!

Thank you




Problem with a DLookUp.
I am currently working in MS Access 2007 and I attached the following code to a unbound text box which is located on a tab control

=DLookUp("[ProdSpecAdr]","t_Suppliers","[pk_SUP_id] =" & [Forms]![f_DE_CPN]![fk_SUP_id])

Everything was working great till I placed this form onto another form making it a Subform... and now my unbound text box is displaying the following......#Name?

Could use some help I've been looking for a fix for awhile now, thanks in advance.




Hello!

I am currently working on an Access 2007 form. This form will be used for taking orders. It then will be printed or saved as PDF. The form gets its information from queries, that are based on live links with few excell spreadsheets. The criteria for the queries are set by the fields on a form. For example, after selecting the customer, one query will search for all avaiable products for that customer. After selecting a product, another query gets the Pack size. To get the "SP - £/kg" and "BP - £/kg" I used a query that uses "Product", "Customer" and "Total (amount of product)" as its criteria.



My problem is with textboxes for "SP - £/kg" and "BP - £/kg". They all use Dlookup function to get their values from the query - It works fine.
However if, lets say, only 1 product is selected on the form, the other 5 rows stay unpopulated. This makes the other 5 "SP - £/kg" and "BP - £/kg" rows stay empty. Unfortnately, this meses up calculations for "Profit" and "Total Selling Price" I managed to identify the problem, that is If I use empty textboxes in calculations, I just get empty fields for results.

My question is, is it possible to make the "SP - £/kg" and "BP - £/kg" fields to be 0 (or at least used as 0 in calculations) instead of being empty? I tried setting the default value to 0, that didn't work. I attempted to use Iff function but I failed miserably. I couldn't find anything helpful on the internet neither. The text boxes are set to 'currency' format. Perhaps there is a solution in the calculations?

If everything else fails, I will just use List Boxes instead of textboxes with Dlookup. Problem with list boxes is that you have to first select the value (note there is only 1 to select), I want to avoid that.

Any help will be greatly appreciated !




Hi everyone! My name is Marina, I am kindda newbie with access and I would like to know if it is possible the following: A have a database with two tables. I would like to create a query comparing the two tables trough a dlookup. Is this possible? My first table would be the one containing data that I would like to check if it appears in the table two.
I am attaching an example database and in the same I would like to have a query that compares me the two tables and bring me information for the person from table two that it is not included in table one. I do not know if I explained myself good. Hope you can help me Attached Files Database4.accdb (348.0 KB, 2 views) Reply With Quote 10-13-2011, 04:56 AM #2 hertfordkc 18 year novice Windows XP Access 2007 Join Date Mar 2011 Posts 468 Why are you using Dlookup? A standard query should be able to handle your problem.




I am new with MS Access and I have developed a small database which is working perfectly. But I have the following issue and I could not do it myself and I tried all the ways.

Please find attached a jpg file "tbl&frm" containing a sample table and form for MS Access 2007. In the table tblAC_Detail, AC_Reg is my primary key because it will never be repeated in my data base.

Now I would like to request your help on how to create a query for the following problem:

When users select AC_Reg on the form, frmFlight_Details, I want AC_Type field (a text box) to appear automatically. For example, if users select AC_Reg as A5-AAA, then AC_Type text box should appear automatically A320-231, if A5-ABA is selected then A319-133, and if A5-AJA then B777-200LR and so on.

Your assistance in this regard would be highly appreciated. Attached Thumbnails   Reply With Quote 12-14-2010, 06:56 AM #2 weekend00 I may not be right Windows XP Access 2003 Join Date Aug 2010 Posts 1,296 let's assume the name of the textbox for AC_reg is "AC_reg", and the name of the textbox for AC_type is "AC_type",
in form design view,
right click on the textbox "AC_reg", select "Build Event...",VB editor will open;
type in following code:
Code: AC_type=nz(dlookup("AC_type","tblAC_Detail","AC_reg='" & AC_reg & "'"),"")




Hi guys I have a problem... I've been creating a database for my work for the purchase department. I'm currently designing the orders form which has a combobox in wich you can select the supplier name and automatically complete de information regarding the data of this supplier (address, phone, fax, etc.)

I'm using a macro which acts after the combobox changes using the setvalue action in which the argument is as follows:

[Forms]![Orden de Compra]![Direccion_OC], DLookup("Direccion","Proveedores","Nombre = '" & [Forms]![Orden de Compra]![cmb_proveedor] & "'")

The "Direccion_OC" field is in the "Orden de Compra" form, this is where I want to put the address of the supplier which is selected with the "cmb_proveedor" combobox. The table that contains the supplier as you can see is "Proveedores" and I search with the field named "Nombre" for the field "Direccion" which contains the address.

when I try to run the macro it appears the missmatch type error

I've already tried checking the type of the data in the tables, both are exactly the same type.

I'm attaching a copy of my database hoping there's someone here who can help its in spanish but I hope I've gave enough detail of the fields involved for you to understand the problem.

Please guys help! Attached Files Compras 2011 v1.0.zip (377.8 KB, 0 views) Reply With Quote 05-16-2011, 09:28 AM #2 pbaldy Who is John Galt? Windows XP Access 2007 Join Date Feb 2010 Location Nevada, USA Posts 9,234 I haven't looked at your db, but I would use this rather than the DLookup's:

http://www.baldyweb.com/Autofill.htm

It would be much more efficient than several DLookup's. If you want to stay with them, the type mismatch error is usually do to syntax inappropriate for the data type:

DLookup Usage Samples




I have finished about half the db for Inventory and Purchase Orders. With my brain, that's impressive!
I am using the same Products table to reference on for Inventory.

I don't believe my Relationships are correct since my Table does not display the correct data.

The Form I am using uses both the Products table and the OrderDetails table and together they can show me everything.

Trying to use 2 different form to both add and subtract from Inventory.
Subtract works fine and shows what the customer has bought and all.

Purchasing, i.e. Buying Products has proved a challenge for me.

I thought that if the relationships were correct, when you added 2 or more tables, they linked by themselves?
If you change the relationship in a query, does it then change it in the Relationships? Attached Thumbnails       Reply With Quote 12-05-2012, 10:48 PM #2 Rod Expert Windows 7 32bit Access 2007 Join Date Jun 2011 Location Metro Manila, Philippines Posts 658 If you change the relationship in a query, does it then change it in the Relationships? No, they are entirely separate things: tables are related such that the RDMS (relational database manager) may ensure data integrity. Tables are joined in a query for the purpose of interrogating and retrieveing the data. Access uses the db relationship (if any) to suggest a join when first designing a query but you may change or delete that suggested join; you may also add joins where associated relationships do not occur in the db.

I'll now take a look at the wrong concern.




Hi all, Access rookie seeking help here. We've got both Access 2007 and 2010 running as front ends for our SQL db on several computers at work. I've got a small Autoexec VBA program to automatically update the Access database that is loaded on everyone's desktop:

Code: '>>Here's the declaration: Option Compare Database Option Explicit Declare Function apiCopyFile Lib "kernel32" Alias "CopyFileA" _ (ByVal lpExistingFileName As String, _ ByVal lpNewFileName As String, _ ByVal bFailIfExists As Long) As Long Sub CopyFile(SourceFile As String, DestFile As String) Dim Result As Long If Dir(SourceFile) = "" Then MsgBox Chr(34) & SourceFile & Chr(34) & _ " is not valid file name." Else Result = apiCopyFile(SourceFile, DestFile, False) End If End Sub '>>Here's the autoexec fxn: Function DatabaseLoad() On Error Resume Next If DLookup("VersionID", "tblVersion") DLookup("VersionID", "tblVersionMasterELISA Log") And Err = 0 Then MsgBox "Your version of " & CurrentProject.Name & " is out of date." & Chr(13) & Chr(10) & "Please reopen the database after it closes.", vbInformation, "Updating database" CopyFile "X:Database.mdb", CurrentProject.FullName Application.Quit acQuitSaveNone End If End Function

Specific error is that the switchboard form on the db does not load using Access 2010 after the update. Everything else seems to work. Also impossible to manually open or design form after the update, since Access says it now cannot recognize data in that form ?! 2007 Access works perfectly, no problems.
I've already turned off everything obvious in the Trust Center, as well as designated my source location as a trusted location. Plz help!




Hi, I'm not a native English speaker, please sympathize for my poor writing.

Please have a look at my database (in my attachment or you can download from: http://www.mediafire.com/?yq4qqgc6j830qgu )

In form1, I want Text13 show email of the customer. The email can be retrieved from table tbl_Customer
However, I tried to use
Code:
=Dlookup("Email","tbl_Customer","CustomerID="&CustomerID) 

but it did not work.

I don't know whether it is a problem in my database or in Dlookup function.
Please help me
Thanks a million. Attached Files DlookupEmail.zip (58.5 KB, 9 views) Reply With Quote 08-27-2010, 04:41 AM #2 maximus Expert Windows 7 Access 2010 (version 14.0) Join Date Aug 2009 Location India Posts 925 Well here is a suggestion without looking at your database is your CustomerID field type is Text or Integer.

I assume u and you have a a a text Box on that form bound to CustomerD

I will type this code to the Cotrol Source of text box 13

When CustomerID is integer:

=DLookUp("[Email]","tbl_Customer","[CustomerID]=" & [Forms]![form1]![CustomerID])

When CustomerID is Text:

=DLookUp("[Email]","tbl_Customer","[CustomerID]='" & [Forms]![form1]![CustomerID] & "'")




Hi everyone

Please have a look at my database (in my attachment or you can download from: http://www.mediafire.com/?did7solw3wbjfwh - 54kb )

I want to create a query that contains all information from table tbl_Booking and this query has to show the price for each BookingID
However, this price must be dependent on TourCode and NumberofCustomer which are in table tbl_TourCode

I'm not sure to use IIF or Dlookup function to solve this problem. Please help me
Thanks a million. Attached Files LookupPrice.zip (53.7 KB, 1 views) Reply With Quote 08-27-2010, 10:34 AM #2 ajetrumpet Banned Windows Vista Access 2007 Join Date Mar 2010 Location N/A Posts 2,698 this does not make much sense:

Originally Posted by doquan0 this price must be dependent on TourCode and NumberofCustomer which are in table tbl_TourCode you can look up the price with dlookup, like what I've shown here, but as far as dependencies are concerned, you need to expand a little bit on what exactly the need is.

As far as your query is concerned, this might be what you want: Code: SELECT tbl_Booking.BookingID, tbl_Booking.BookingDate, tbl_Booking.DepartureDate, tbl_Booking.TourCode, tbl_Booking.CustomerID, tbl_Booking.Numberofcustomer, DLOOKUP(IIF([Numberofcustomer] = 1, "[1customer]", IIF([Numberofcustomer] BETWEEN 2 AND 3, "[2to3customer]", IIF([Numberofcustomer] BETWEEN 4 AND 6, "[4to6customer]", IIF([Numberofcustomer] BETWEEN 7 AND 10, "[7to10pcustomer]", NULL)))) , "tbl_TourCode", "[TourCode] = '" & [TourCode] & "'") FROM tbl_Booking; Also, there are numerous problems with your data:

*No of Customers is the currency data type.
*7 to 10 customers field is named 7to10pcustomer. TYPO.
*The data is not normalized.

Try some research on Access. Maybe naming conventions. It will help a lot!




I'm trying to add a new capability to a database I wrote for an insurance company. One of their business models is to return quotes to the requester within 12 minutes, and they are very serious about doing this in every case.

I already have a routine written which stamps the current time at the time a quote is initiated, but it's used to simply print the elapsed time from start to the quote printing for information purposes. They have asked me to expand on that. They would like admin users to have a message pop on their screen when a quote has been initiated 10 minutes prior so that they are aware of any agent that is having trouble meeting the requirement and can help if needed.

There is no admin level set up, as it is a small office and has not been considered necessary.

I can envision an addition to the AutoExec macro that runs at startup and opens forms that will be used which notes which user is opening that specific computer. If it's one of those designated as admin then a timer routine would run every minute. It would do a DLookup on a Query which returns the username of anyone who has a quote 10 minutes old or older with no print time. If the DLookup Is Not Null, then the Query would open on admin screens showing them who, what time, etc.

The Query is easy to construct, and getting it to open on timer command is not a problem. The issue I'm not sure how to solve is to get the individual work station to recognize that it has been logged onto by one of those users considered as admin.

Does anyone know how to identify the user at logon and run certain commands only for those users? If I can get that part of it working I can make the rest operate as intended.

Assistance would be appreciated.




Ok first let me say, before i started anything on the data base, i saved it in the 2002-2003 format. Now with that said let me give some details of the problem. (I have highlighted code and errors in red to try and help keep it from being so jumbled)

1. I have a field identified as "Submitted By" which captures the computer name that the form is being filled out from and automatically populates it. It uses the following module:

Function udfGetUserName() As String
On Error GoTo err_udfGetUserName
Dim strUserName As String
strUserName = Environ("username")

exit_udfGetUserName:
udfGetUserName = strUserName
Exit Function
err_udfGetUserName:
strUserName = ""
Resume exit_udfGetUserName
End Function

Then in the default value field i have :

=udfGetUserName()

Now this works fine when working from a computer with access 2007, however in 2003 i get the error :

Compile Error: Can not find project or library

When Looking at the debugger it specificlly highlights the Environ in the line strUserName = Environ("username")

-----------------------------------------------------------------------
2.I have 2 seperate fields on the form that populate date and time with the following default values

=Date()
=Time()

Again, these work fine in the 2007 version, and not in the 2003. I even went in on a 2003 machine and used the build expression function for date to verify i had the correct formatting, still in these to fields it only gives the following info when the form is started:

#Name?

-----------------------------------------------------------------------

3. I have a Customer Number Combo box the user can select from. On update of that field i have the following lookup preformed to auto populate 3 other fields. The after update code looks like this

Private Sub Combo165_AfterUpdate()
Dim HOLD As String
Text121 = Nz(DLookup("[EMAIL]", "Customers", "[Forms]![ProblemLogCustomer]![Combo165] = NANUM"))
HOLD = Nz(DLookup("[PHONE]", "Customers", "[Forms]![ProblemLogCustomer]![Combo165] = NANUM"))
CustPhoneNo = Left(HOLD, 12)
CustomerContact = Nz(DLookup("[CONTACT]", "Customers", "[Forms]![ProblemLogCustomer]![Combo165] = NANUM"))
End Sub

This works great for the 2007 versions, not at all on the 2003. I get the following error:


Compile Error: Can not find project or library

With that error it specificly highlights the Left in the following line of code:

CustPhoneNo = Left(HOLD, 12)





I understand there is a lot of info listed, and that I may have left out some important parts in trying to describe the problem. Please fill free to ask any questions and i will do my best to anwser them. Just for informational purposes, this is desgined to be a Help Desk/Ticket program for our company to log issues both internally as well as at the customer level. Thanks in advance for any help!




Thank you in advance for your assistance.

I am a relative Access 2007 Newbie and am having a problem with a form I created.

This is a variation on a Order Database.

I have a table Products with a field RetailPrice that gets its value from a table BoxStyle from a field BoxStyleRetailPrice in an On Change Event. I have used this code.

Private Sub CabinetStyle_Change()
RetailPrice = DLookup("BoxStyleRetailPrice", "tbl_BoxStyle", "BoxStyle_ID=" & CabinetStyle)
End Sub

This returns the value I need.

In a later form I need to retrieve the RetailPrice Value from the Products Table for a field in the OrderDetails table called Price.

In a SubForm Called OrderDetails I am trying to use the same On Change Event in The ProductID Field to get the value I need.

I have tried this code

Private Sub Product_Change()
Price = DLookup("RetailPrice", "tbl_Products", "Product_ID=" & Product)
Quanity = 1

End Sub

I get an error.

Run-time error `3075`:

Syntax Error in query expression 'Product_ID= '.

Not sure what I am doing wrong.

Thanks again for your help

Jason


Page 1 of 2.
Results 1...20 of 36