DLookUp Works Great BUT
Feb 28, 2008
here is the code I put in the OnExit of the InDate
If DLookup("[BeginDate]", "GroupEvent", "[In Date] >= [BeginDate] And [In Date]<= [EndDate]") Then
It works great BUT I need to get the Activity and Number field information from the record. I cannot use another DLookup cause it pulls the Activity information from the first record and not the found record. Any Ideas?
GroupEvent Table :
Fields are BeginDate, EndDate, Activity and Number
Thanks
Ed
View Replies
ADVERTISEMENT
Aug 4, 2006
Drag and Drop works great but how do I open a reports based on what I selected?
I have two list boxes the first is "items not selected" and the second is "Items selected" now I want to create a button that open a report showing the result from "Items selected"?? Hope someone can help I've been to ms access web site but our firewall does not allow any down loads. Thanks in advance.
View 3 Replies
View Related
Jul 15, 2005
Good afternoon, I have a form with a subform and in the first txtbox of the subform in the GotFocus event I have a little procedure which checks the txtboxs on the parent to make sure that there is data in all four of the txtboxes. This works great the first time and it pops up a msgbox and it even setsfocus on the txtbox with no data in it, but if I tab into the subform a second time and there still is no data in one of the txtboxs on the parent form, nothing happens, no message and no setting focus on the txtbox with no data in it. Does anyone know of a way to get this procedure to re-set everytime a user tries to enter the subform? Thank you in advance to anyone offering and ideas and suggestions.
View 8 Replies
View Related
Mar 26, 2015
basically am creating a booking system, i have a add a room form. my form should check whether i already have a room number in my table, which works when the form is filled in. however when my form is null, then i press add new room button, i get this error rather than " please fill your form in"
Error: runtime error '3075' syntax error (missing operator) in query expression 'Room Number ='.
room number is a number field, integer but has primary key. i cant keep autonumber, as my requirement is to add new room number, but the roomnumber has to be unique.
here is the dlookup;
If DLookup("RoomNumber", "tblRooms", "RoomNumber = " & Forms!RoomPackages!txtRoomNumber) > 0 Then
MsgBox "This number already exists."
Else
p.s it unbound form
View 3 Replies
View Related
Oct 21, 2005
Ok people i appreciate this might me a bit long winded but ANY help would be appreciated.
i have a table called "Customer_Details" and i have 3 other tables calls
"tariff 1", "tariff 2" and "tariff 3".
The Customer_Details table has a form with the same name and on that form there is a field called "tariff" which is a drop down box with tariff 1,2 and 3 in it.
Also on the Customer_Details form is a Balance field which is linked to the same on the table.
finally there is a table called "data_live" this is continually added to on a day to day basis, the fields are : Acc No, Cost, Prod ID.
on the tariff tables each product has a product id
Basically want i want to do is on the customer form select a tariff, then when the data is entered into data_live the correct tariff is apllied to the product (via product id) then i think i am write in saying that i will be able to create a query that sums up all the prices for that customer (via Acc No) and displays the result in the Balance field.
any help at all would be great
many thanks
View 1 Replies
View Related
Jul 24, 2006
My friends,
I am trying as hard as I can to learn Access. I am developing a database for my signage design business. I've been at it for about 5 days already. I know this question would be a piece of cake for the experts in this forum.
When one of my artists finishes with an artwork, he will save this as a jpeg file. I want him to upload it to the database via a form.
1. I would like to come up with a command button that, when clicked, will open a browser on the users remote computer and enables him to find the file to upload.
2. When this file uploads, I want it to be saved in another directory, outside the db, and then linked to a field within the db.
3. And I want this feature also for mp3 files.
Can any one help with this?
View 3 Replies
View Related
Aug 23, 2005
There is an Access application somewhere in shared drive.
100 users open this application using a batch file which copies this database on their c: . They start using this database and the master database gets replicated real time. I am not sure if all the database are replicated as it is copied everyday ( so that each one of them has latest version of the application )
Is it possible ( I know it is :) ) but i am not able to figure out how it is happening.
Please help :confused:
View 2 Replies
View Related
Dec 9, 2005
What would trigger an error to occur if there has been no changes to a DB.
My error # is 2427 (You entered an expression that has no value).
This error occurs when I click on a command button to open a report. When I debug, it sends me to an IF statement that I have loaded in the On Format of the report.
This worked perfectly fine before - the If statement is simple, if a value is true, then it changes a box to bold and if the value is false, the box in the report remains the same weight.
I am not understanding why it is saying that I have entered an expression with no value when the IF statement reads both the true and the false of a chkbox and adjusts the box accordingly.
Can someone explain why this is occuring? Im litteraly stuck.
View 14 Replies
View Related
Dec 27, 2005
Hi,
I have a form which has two separate subforms in it. When the user selects a record in the first subform, I want the values of that record to write to the second subform's fields. OnCurrent works when there is more than record in the subform. My problem is two fold, one I don't want my copy code to run when the form loads and I need it to copy a record when there is also only one record in the subform.
Which event do you recommend?
Also, does anyone have a suggestion on how to allow the user to tab through a form, but really only record by record (with the keyboard) and not having to go through all the fields before getting to the next record.
Thank you,
Colette
View 1 Replies
View Related
Jun 30, 2006
Hi, I'm a little confused here. I'm using the following on one DAP and it works just fine. When I add it to another page, it does not work. Any ideas?
<SCRIPT language=vbscript event=onclick for=Save>
MSODSC.CurrentSection.DataPage.Save()
msgbox "Record Saved - you may continue to edit or exit your browser.",64,"Saved"
</SCRIPT>
Thanks...Doug
View 1 Replies
View Related
Apr 18, 2006
Hi All,
I have a text box (memo field) that the user enters a description. This is sometimes more then one paragraph. In a text box you cant hit enter and goto the next line (or paragraph). I would like to to this? can it be done?
Thanks
View 1 Replies
View Related
Apr 18, 2006
Hi All,
I have a text box (memo field) that the user enters a description. This is sometimes more then one paragraph. In a text box you cant hit enter and goto the next line (or paragraph). I would like to to this? can it be done?
Thanks
View 5 Replies
View Related
May 12, 2005
Can someone please help me. I was in the code view of my database and accidentally deleted a few lines of code and now everything i do is coming up with the same error message
Module Not Found
For every form I try to open it does this and even my buttons to close form and to exit system do not work.
Has anybody had this problem before?
Would anybody mind taking a look at it if I sent a zipped version? my email and msn is nicholaseary@hotmail.com
View 2 Replies
View Related
Aug 16, 2005
I have installed on my PC, WindowsXP sp.1, MSaccess versions 97 and XP.
After I have tested some databases, MSaccess 97 don't works successfully more, but XP version works good. I can't modify files mdb. Access works like in run-time mode: bars and commands reduced, standard icon of access absent, database window absent, ecc.
If macro autoexec or a form that play at the start of mdb are present, mdb works but it is impossibile to modify it. Otherwise mdb dont works, I can see access window with only menu files and window.
I have removed access and after yet installed it, but it don't work successfully.
maybe other application leaved files (or modification in file registry) that install procedure of access97 cant rewrite? And that dispose access 97 to work in run-time mode (or like)?
I am hopeless. Can You help me? Can you give me a list of files to remove, or list of modification to do in registry?
Danit :
View 4 Replies
View Related
Oct 24, 2005
I'm using Access 2000 along with Oulook 2003. Since we changed email servers, the send report no longer works. The addresses no longer appear to be valid. I can manually put all the names in every time an email is sent, but that is defeating the purpose!
I even tried changing the addresses to what an outsider would use rather than our internal name list; (milko@valspar.com instead of Mary Ilko) but this didn't work either.
Here's the code:
Private Sub cmdMailApproval_Click()
OnError GoTo Err_cmdMailApprovalClick
Dim stDocName As String
stDocName="rptForApprovalReport"
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport stDocName, acViewPreview,, "[Batch#]=forms![frmInbound]![Batch#]"
DoCmd.SendObject acSendReport, StDocName, acFormatSNP, "FirstName Lastname",,,"Pre SHip Approval", "Please see the attached."
DoCmd.SendObject
Thanks in advance for your help!
Mary :o
View 1 Replies
View Related
Nov 14, 2005
Hi
I am using the following vba / SQL to create a query which looks between two dates in a table. It seems to work first time but thereafter my query pulls back dates which are not in the criteria (dates are selected from Combo boxes - search_chasestartdate and search_chaseenddate):
If (Search_ChaseStartDate <> "" And Search_ChaseEndDate <> "") And intPosition = 0 Then
strParameter = strParameter & "Sales.Chase between #" & Search_ChaseStartDate & "#And#" _
& Search_ChaseEndDate & "# "
intPosition = 1
Else
If (Search_ChaseStartDate <> "" And Search_ChaseEndDate <> "") And intPosition = 1 Then
strParameter = strParameter & "AND Sales.Chase between #" & Search_ChaseStartDate & "#And#" _
& Search_ChaseEndDate & "# "
End If
End If
If strParameter <> "" Then
strSQL = "SELECT Sales.* FROM Sales WHERE " & strParameter & ";"
Set qdf = dbs.CreateQueryDef("Business Master Query", strSQL)
DoCmd.OpenQuery "Business Master Query", acViewNormal, acReadOnly
Else
MsgBox "You have not selected any parameters to search on. Please try again.", vbOKOnly
End If
Does anyone have any idea why this is happening? I have been stuck for a while on this.
Thanks!
Mark
View 5 Replies
View Related
Oct 4, 2006
Hi,
I have written a subquery that works fine alone, but it returns -1 when with query.
The subquery is supposed to return a total of type currency.
Any help/suggestions will be very much appreciated.
SELECT tblSite.Name, tblPhase.Phase_No, tblVariation_Order.Customer_No, Exists (SELECT Sum(tblVariation_Order.VO_Price)
FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Custome r_No
GROUP BY tblCustomer.Customer_No;) AS Expr1
FROM (tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No = tblVariation_Order.Customer_No) INNER JOIN (tblSite INNER JOIN (tblPhase INNER JOIN tblHouse ON tblPhase.Phase_No = tblHouse.Phase_No) ON tblSite.Site_No = tblPhase.Site_No) ON tblCustomer.Customer_No = tblHouse.Customer_No
WHERE (((Exists (SELECT Sum(tblVariation_Order.VO_Price)
FROM tblCustomer INNER JOIN tblVariation_Order ON tblCustomer.Customer_No=tblVariation_Order.Custome r_No
GROUP BY tblCustomer.Customer_No;))=True));
View 5 Replies
View Related
Jun 25, 2007
I've got a number of different append and delete queries running on command on one of my forms, in which it makes a copy of all of the data on that form and included subforms, copies them into another table, and then deletes all of the data from that record. When this runs, I get an error saying "record deleted" and then another error message. Now it is deleting the records and it is moving them, however when i go onto one of the subforms, all the fields in the subform show "Deleted#" until i go back to the previous record or forward to the next, then they clear, but everytime it brings up the other error message it says "end or Debug" which I don't want it doing.
Is this the query? Or the coding?
Thanks
View 7 Replies
View Related
May 12, 2005
It's been a while since I've been on the boards. Good to see lots of the same folks are still here. :D
I've got an interesting problem. I have a sub that calls another sub. Pretty simple there. The problem is that when sub A calls sub B, sub B does not appear to run. This is all sub B does:If Not IsNull(Me.txtNumber) Then
Me.txtNumber = Me.txtNumber + 1
End If
The weird thing is, sub B runs just fine if I run the sub B in step mode. If I set a break point and use F8 to step through the code line-by-line, it runs just fine. If I take out the breakpoint, the code no longer works.
Anybody have any ideas? I'm running Access XP Sp2 on Windows XP SP1. Thanks.
View 3 Replies
View Related
May 9, 2005
Hello All,
I have MSAccess ade working in some computers which has WindowsXP-pro(SP1 and SP-2) without any problem. But It doesnot work(When i said it doesn't work means it doesn't work in certain parts which i will give an example) in some computers that has exact same configaration. The problem is when I try to instance a class using a TypeName function, it doesnot recognize the class name and just returns an object rather than recognizing the class's name. Its a strange thing because I have some .ade from past works fine in all machines. The only difference is In the new .Ade is I have included the Microsoft outlook 11.0 object library. But the part of the code thats not working doesn't even use the Outlook library at all. So did anyone has this kind of problem, PLease suggest or ask me if you have more details of this problem. I really appreciate your help.
Thanks again
ds111
View 2 Replies
View Related
Sep 16, 2005
I have a qry I need for picking the info for sending a series of letters out. I finally got it to work and now Im wondering if there is a better more efficient way. The qry I call to generate my report calls on information from 10 other queries and 7 tables. Some of the other queries are needed for other sub-forms that rely on the data. Is this something I should change? or just be happy it works???
-Dan :D
View 1 Replies
View Related
Apr 5, 2006
Situation: I have written a fairly straight forward nested query which aggregates sales & activity data (num_calls, date, etc.) by customer specialty (CODE_ESP_ACC1) in a single query. The SQL I have written to achieve this is as follows:
SELECT Q002.GP, Sum(Q002.Num_Calls) AS Total_Calls
FROM (SELECT Q001.CODE_ESP_ACC1, Count(Q001.ACT_ID) AS [Num_Calls], Iif([Q001].[code_esp_acc1]="MG" or [q001].[code_esp_acc1]="MF",1,0) AS GP
FROM (SELECT [06 Act 2005 Hosp - IM].ACT_ID, [06 Act 2005 Hosp - IM].TER, [06 Act 2005 Hosp - IM].ACC_ID, [11 Physicians with main specialty].CODE_ESP_ACC1, [06 Act 2005 Hosp - IM].DATE
FROM [06 Act 2005 Hosp - IM] LEFT JOIN [11 Physicians with main specialty] ON [06 Act 2005 Hosp - IM].ACC_ID = [11 Physicians with main specialty].ID_ACC
WHERE ((([06 Act 2005 Hosp - IM].POSITION)=1))) AS Q001
GROUP BY q001.code_esp_acc1, Iif([q001].[code_esp_acc1]="MG" Or [code_esp_acc1]="MF",1,0)
HAVING (((Q001.code_esp_acc1) Is Not Null))) AS Q002
GROUP BY Q002.GP;
This works absolutely fine. I can save this query and run it many times without problems. However, if I then re-enter SQL edit view, Access reformats it as follows:
SELECT Q002.GP, Sum(Q002.Num_Calls) AS Total_Calls
FROM [SELECT Q001.CODE_ESP_ACC1, Count(Q001.ACT_ID) AS [Num_Calls], Iif([Q001].[code_esp_acc1]="MG" or [q001].[code_esp_acc1]="MF",1,0) AS GP
FROM (SELECT [06 Act 2005 Hosp - IM].ACT_ID, [06 Act 2005 Hosp - IM].TER, [06 Act 2005 Hosp - IM].ACC_ID, [11 Physicians with main specialty].CODE_ESP_ACC1, [06 Act 2005 Hosp - IM].DATE
FROM [06 Act 2005 Hosp - IM] LEFT JOIN [11 Physicians with main specialty] ON [06 Act 2005 Hosp - IM].ACC_ID = [11 Physicians with main specialty].ID_ACC
WHERE ((([06 Act 2005 Hosp - IM].POSITION)=1))) AS Q001
GROUP BY q001.code_esp_acc1, Iif([q001].[code_esp_acc1]="MG" Or [code_esp_acc1]="MF",1,0)
HAVING (((Q001.code_esp_acc1) Is Not Null))]. AS Q002
GROUP BY Q002.GP;
... and this no longer works (syntax error in FROM statement).
To save you sifting through the SQL, the error is caused by Access replacing the soft brackets () aroung the outermost FROM statement with an object identifier-style hard bracket []. - specifically, the outermost SELECT statement now reads
SELECT Q002.GP, Sum(Q002.Num_Calls) AS Total_Calls
FROM [
instead of
SELECT Q002.GP, Sum(Q002.Num_Calls) AS Total_Calls
FROM (
and the alias for the subquery reads
]. AS Q002
instead of
) AS Q002
Changing the Access alterations back to my original fixes this problem - so the problem is more an annoyance than a disaster. However, I have no idea why Access is doing this to my query, nor how to prevent it from doing so. Does anybody have any experience of this behaviour in Access and any ideas of how to fix it?
Help! Replies appreciated.
View 1 Replies
View Related
Oct 1, 2007
i posted about this the other day, thought i sorted it and now it keeps coming back to haunt me
i have an append query in my database it is based on 3 other queries
when the query is run using vb
DBEngine(0)(0).Execute "QRY_ImportDuplicatesTopLevel", dbFailOnError
or by putting its sql into a string and executing using CurrentProject.Connection.Execute strSQL
i get an error message saying too few paramters - expected one
i'm fairly used to these messages which mean that in the query is a non existent or misspelt field and i find them by running the query in the query viewer and letting access highlight the missing / misspelt field
the weird part is that when i run this query as a stored object by clicking on its icon i get no error and the query does the job it is supposed to do
does anyonw have any idea why this situation could occur?
View 4 Replies
View Related
Aug 3, 2005
i have a DB to manage tasks. The main table, which contains information about the task on itself, is populated by a form. This form has a subform to add subtasks which are stored in another table.
The problem is that i can check if the information entered in the form is correct, but i can not do the same in the subform. The subform get information from the table where those records are stored. I tryed using before update and after update but it did not work. Also, it seems to be checking allways the first record.
example:
task 5 has the following subtasks: A,B,C,D
if i try to check the values it allways check against the first subtask (A).
Private Sub subtask_AfterUpdate()
MsgBox "Please, fill the DESCRIPTION field."
Me.taskdescription.SetFocus
End Sub
Private Sub subtask_beforeUpdate(Cancel As Integer)
If Me.subtask = DLookup("[subtask]", "change_desc", "[change_id2]=" & Me.change_id2 & "") Then
MsgBox "This task letter already exists."
Me.subtask.SetFocus
End If
End Sub
thx in advance, Max.
View 10 Replies
View Related
Oct 5, 2005
I have an unbound text box that displays when certain criteria exits, which I placed in the Form's properties, "On Current" ...
If [Decor6Date] <= Date - 30 And [NonrecommendQuestion] = 0 And IsNull([DateDecReceived]) Then
[LateDecoration2] = "Late Decoration"
Else
[LateDecoration2] = ""
End If
I then made a timer event to show/flash the message
Me!LateDecoration2.Visible = Not (LateDecoration2.Visible)
Everything works fine ... the problem is the message will only flash if I exit the record then return to the record. How can I fix that problem? I tried Refresh ... no luck.
Any help would be appreciated.
View 1 Replies
View Related
Sep 2, 2004
I have an Excel macro that formats a chart and I have tried copying and adapting it for Access.
The following line is executed OK in Excel :
Select Case ActiveChart.SeriesCollection(Counter).Name
I have changed it to the followig for Access
Select Case Me!DERVGraph.Object.Application.Chart.SeriesCollec tion(Counter).Name
but in Access I get an error 'Object does not support this property or method.
(The space shown in SeriesCollec tion above is a screen bug)
Any ideas?
Thanks
View 8 Replies
View Related
