When I added this field/function to the query, I got #error values and when I clicked on one, I got an error that said:
The expression you entered as a query parameter produced this error:The object doesn't contain the automation object 'Please Enter Year'.'
I assume the problem is that I have the user filter the report by use of the Like() function for [Year] in the query when the report is accessed. How do I tell the DLookup function to search the column as filtered by the Like() function?
I have a query that prompts the user to display certain/all of the members in my member table. The problem is, using a parameter query in the memberID field only allows the user to send the message to one member, not multiple members. "1 or 2 or 3" or "1,3" obviously don't work. I've tried creating a form with a list box to select the members (which I;ve done) but obviously you need code and stuff to get it to work which I think will be too complicated. Is there a simpiler way or is the form the only thing that will work? Thanks Kris
How can I have a user enter the WHERE criteria in the Set Rs statement below.
Set Rs = CurrentDb.OpenRecordset("SELECT [Net] FROM [tblRecap] WHERE [Yr] = 2000 and [Event] = 'lancaster'")
I would like a message box to ask the user to define the [Yr] = and [Event] = fields since they would change from time to time and then pass the values to the OpenRecordset method. Is this possible? Can someone help supply the code?
A parameter query won’t work because when you manipulate these queries in DAO through VBA you need to supply the parameter value before you open the recordset object. If you don’t DAO generates an error.
Here is my code:
Option Compare Database Option Explicit
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Dim ConsqWin As Long Dim ConsqLoss As Long Dim tmpWin As Long Dim tmpLoss As Long Dim Rs As DAO.Recordset Set Rs = CurrentDb.OpenRecordset("SELECT [Net] FROM [tblRecap] WHERE [Yr = 2000 and [Event] = 'lancaster'") Do While Not Rs.EOF Do While Rs!Net > 1 tmpWin = tmpWin + 1 Rs.MoveNext If Rs.EOF Then Exit Do Loop If Rs.EOF Then Exit Do If tmpWin > ConsqWin Then ConsqWin = tmpWin tmpWin = 0 Rs.MoveNext Loop
Set Rs = Nothing
End Sub
By the way the YR field is a number and not a date so the WHERE clause doesn’t need #2000#
I am trying to get my VBA code to dump a query once the user pushes a button. I have the following code to call up the Excel app.
Code:
Option Compare Database Private Type BROWSEINFO hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long
[code]....
The qry_PP_Errors_Union is a Union query. In this query there is a date field. I would like to be able to to use that date field as a parameter. So I have written this VBA to prompt the user for a Begin Date and an End Date.
Now the part that I am missing is that I am not sure how to make the "strBegindate" and "strEnddate" the criteria for the union query.
The following is the SQL for my union query.
Code: SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, UICError AS Error, "Update and Internal Correspondence" AS Category FROM qry_PP_UIC_Error UNION SELECT LastName, FirstName, Title, TeamName, WorkOrderNumber, DateCompleted, WorkCode, BIDError, "Bids" FROM qry_PP_Bid_Error
[Code] ....
Without the criteria, my code works for dumping everything out into Excel. However, dumping all the data results in a 7 mb Excel file that requires manual deletion of the information that is not pertinent.
I am building a form in access and I am trying to find a way where user input isn't possible in the associated textbox when "No" from on option box is selected.
I'm using the code below to check new product# input from a form called frmProducts that is bound to a table called tblProducts.
Private Sub Prod__BeforeUpdate(Cancel As Integer) Dim varX As Variant varX = (DLookup([Prod#], "tblProducts", Forms!frmProducts.[Prod#])) If Not IsNull(varX) Then MsgBox [Prod#].Value & " already exists as a product #" Me.Undo Cancel = True Else 'do nothing! End If End Sub
The problem is that any new product# that you input results in "product already exists".
I created a form that allows the user to choose the criteria that they want to see on a report using =Forms!formname!controlname in my query. It works great but I want to also allow the user to choose nothing and return all records instead of limiting them to choose just one type of record. Is this possible? Before I created the form my query had the [Enter parameter] on one criteria line and [Enter parameter] Is Null on the next criteria line and that was working great for my use but I need to create a simple form for other users.
each boarding point has 6 different pickupcodes and pickuptimes
i am trying to create a form, which is currently displaying data from an query depending on which tour is selected from the main form.
my current dlookup is like this. =DLookUp("[pickuptime]","boardingpoints","[boardingpoint]=Forms![pickup list]![boardingpoint]")
what do i need to add to the end of this so that it also looks at the pickupcode as well as the boardingpoint to display the correct pickuptime and not just the first one it comes to?
I'm reasonably new to all this, and have read many posts and got most of my answers from reading the posts here! THANKS.
However I need some help with the following: I have a form that allows someone to input information about products being booked in. However sometimes we have the same product from the same supplier with the same invoice number etc.. but they have individual serial numbers. So the scenario is I have ten items being booked in, they are all the same product from the same supplier but they have individual serial numbers. I have created a do while loop where I can duplicate all the information for each product, but I need it to pop up a box asking the user for the serial number each time it goes through the loop. He is what I have so far.
Dim MyQuantity As Integer Dim TempSerial As String Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("BarcodesDB") MyQuantity = 1 Do While MyQuantity <= Me.Quantity 'insert into table using either a recordset or Append Query rs.AddNew rs!Supplier = Me.Supplier.Value rs!Date = Me.Date.Value rs!Invoice_Number = Me.Invoice_Number.Value rs!Pcode = Me.Pcode.Value rs!Item = Me.Item.Value rs!Quantity = 1 rs!Serial_Number = Me.Serial_Number.Value 'Get this value from user each loop rs.Update MyQuantity = MyQuantity + 1 Loop rs.Close Set rs = Nothing
I have an inquiry about using dlookup to retrive all fields from a table that are using the same criteria. Here's the table structure
Table1: ID (Autonumber) Number Code Grade
I have the following data: Number Code Grade 4 15 68 4 52 78 4 17 69 5 85 83 5 28 17
I am using this to retrive values for number 4 but I only get the first row DLookup("Code", "Table1", "[Number] = 4") DLookup("Grade", "Table1", "[Number] = 4")
I get only as a result 15 and 68, how can I retrive all the rows that have Number 4?
I have the following DLookup statement which if the number of bags is greater than 0 will find the previous accommodation name. How do I get it to only select the accommodation name if the CustomerID field is the same as the previous as well?
I'm back yet again with a new and intriguing set of problems. I am building a fancy report and Come to find out you cannot use SQL to return one simple number in a text box because even though you know you will write the statement to return only one answer everytime, access doesnt understand that so we can do a domain aggregate thing with DLookup. OK fine I have to do alot of them but still OK, here is my issue I can get DLookup to give me what I want if i type into each control source the criteria that I want to return.
I want to lookup a number from a recordset but there are two things that make the number unique date and strain. strain is easy to cough up in the right place because i will hard code that into the control source dlookup, the part that is killing me is the date I can get a dlookup to give me accurate results based on multiple criteria, but how can i get that to include the date for which I am running this report. I do enter the date parameter each time i open the report.
Here's what i got so far
=DLookUp("csQuantity","SpawnScheduleCulSpawn","csstrain='AC-PA'" & " AND week = #4-29-07#")
That returns the answer i want, but i have 50 of these text boxes in one report so i cant go through and write the date in each one.
how can i make the part following " AND week =" dynamic so that it runs the date that i entered as the parameter for the report?
I am using MS Access 2010 and have an issue with one of my forms. First off, I have a table called coefficient which contains a column MIN, a column MAX, and a column COEFF.In my form, called CALC, I want to grab a number that it is in textbox [Text12] and place the related coefficient number in textbox [Text15].I placed a control source in [Text15] using =DLookup("[COEFF]","coefficient","[Text12]>[MIN] And [Text12]<[MAX]").
I am trying to make user input forms for each of the tables in my current database. I have made the forms, but I am required to have the forms come up blank first (so they can create a new record), but allow the user to edit existing records too. How do I do this?
creating a DLookup expression for an unbound control on a report. This report displays data collected from the users from a table called tblaudit completed. This table contains (among others) three fields labeled below
Brand Form Area Changed
I also have another reference table labeled refevalareas. The fields in this table are as follows
BrandName FormName AreaName PointValue
I would like to create an unbound control on my report that "prints" the point value depending on the Brand, Form, and Area displayed for each record. Though the field names are different the data collected and displayed in each table is consistent. I haven't worked much with Lookups and the logic is challenging for me to grasp but I think I have the basic idea which is shown below..
I'm having trouble with DLookup() using dates as part of a multiple criteria. I'm using the following;
Code: If IsNull(DLookup("ExchRate", "TBL_DDPExchRates", "CurID = " & Me.Combo4 & " AND ExchDate = #" & Me.ShipOBDate & "#")) And Me.Combo4 <> 2 Then
It seems that if ShipOBDate is any date between the first and tenth of the month the DLookup fails to locate the appropriate record. I can't understand why as ShipOBDate and ExchDate are both formatted as Short Date and ExchDate is being populated via an OpenArgs which is derived form the field ShipOBDate..Is the fact the the date is getting converted from a date to string and back to a date some how upsetting things
I have a database which I have been using for over a year now which includes a Dlookup formula in a report.The dlookup references the query QRYDwgCount and looks up the number of drawings issued by a particular architect. I have been using the following formula which was previously working
The problem I have now encountered is a new job with an architect that worked on another job already in the database. Instead of bringing forward the number of drawings issued by the architect for this current job, the formula is showing the number of drawings issued for the earlier job. I therefore need to add "ContractName" = [ContractName] but I am getting either Error or an incorrect answer as the result.This is what I have written:
Hello, all. I have created a project in Access 2003 to track a student's progess toward a college degree. I want the user to enter their student id in a text box on a form, look up if that user is present in the student table, and if not show the form to enter their student information.
I can't get the text box to accept user input, and return the student info as a single record on the form. I have a query but I don't know how to set the proprerties to run the query.
I want to put a textbox on a form that will take user input (a postal code) and put it into a table. I'm not sure how to do this. Is it even possible, and if so, any suggestions?
