Searching A Query Using Pre-Defined Crieria On A Form :o
Apr 17, 2008
Hi there guys;
I'm after something quite complicated this time, so i'm going to do my best to explain it in the offchance that someone can help me out. I'm not sure that what i want is even possible, although i think it should be; i just dont know how to achieve it.
I have three entities; Table1, Query1, Form1.
Form1 and Query1 have both been created using Table1 with some basic data.
Form1 will have three text boxes titled:
Broker; Audit ID; Audit Date
Beneath these it will then have a search button.
The idea is that i will type "BrokerName" into the "Broker" field on the form, and using Query1, the search button will retrieve all lines of data that have "BrokerName" in the "Broker" field in the query. So it's like using the [Enter Broker Name] criteria in the query except the criteria is entered into a Form text box rather than a pop-up window. Does that make sense?
The remaining two fields, Audit ID and Audit Date, will need to ADD the data entered into those boxes on the form, in to the query. I *think* it needs to add the data in to the query before "BrokerName" is used to select the records with "BrokerName" in them.
After the records have been found using the "BrokerName" criteria, they would then be inserted as Subform1 on Form1. I figured i would need a refresh button for this to work effectively. I have already made a refresh button, but if anyone knows of a way to refresh it automatically after selecting the records i need, then that'd be great.
So i have the basic setup ready to go, i just dont know how to make Query1 use the criteria entered on Form1 to search specific data.
I really hope that makes sense, i'm not very good at explaining. Any help is hugely appreciated!
Thanks again.
View Replies
ADVERTISEMENT
Apr 20, 2015
I copied some VBA from one database to another. I didn't change anything and I am able to run it fine in the first database. But in the DB I pasted it to, I am received a Compile Error message with the XlApp As Excel.Application area highlighted.
Function OpenAutoCount()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
[code]...
View 3 Replies
View Related
Feb 10, 2014
I have a line of code in an old program:
Dim CN as ADODB Connection
This is giving me the error 'User defined type not defined'. I know I have to set something in a list somewhere but have forgotten how to do that. Where to go, and what to set?
View 1 Replies
View Related
Dec 16, 2006
Hi,
I have a form that is based on a query with many fields. As an example there is a field called "Medium" which contains values such as "Oil on canvas", "Oil on wood" etc.
At the moment I have a filter which works fine but this doesn't allow me to search all records which have the word "Oil" as part of the Medium.
I would like to be able to search all fields on the form. I have looked at a few exmaples on the forums but could not find one that applies to my scenario.
Can anyone help?
Thanks in advance.
View 4 Replies
View Related
Nov 9, 2005
Hi,
On Compiling my assecc database VB code I get the following error message "User defined type not defined". I understand it is beecause I have not declared the Variable Type, but have no idea to exactly which part of the code the error is referring to.
How do I find out WHICH User defined type is not defined, especially when I have not got any (or do not want to use any) user defined types?
Thank you in advanced programming wizards. Kind regards, Adam.
View 14 Replies
View Related
Oct 23, 2006
Hello,
I have the following code and i don`t know what's wrong
Private Sub cmdCautare_Click()
Dim strSQL As String, strOrder As String, strWhere As String
'Dim dbNm As Database
'Dim qryDef As QueryDef
'Set dbNm = CurrentDb()
strSQL = "SELECT DOSARE.DosarID,DOSARE.DenumireDosar,DOSARE.CodDosa r,DOSARE.DataDosar,DOSARE.Denumire,DOSARE.Data,DOS ARE.Stadiu FROM DOSARE"
strWhere = "WHERE"
strOrder = "ORDER BY DOSARE.DosarID "
If Not IsNull(Me.txtDenumire) Then
strWhere = strWhere & "(DOSARE.DenumireDosar) Like '*" & Me.txtDenumire & "*' AND" ' "
End If
If Not IsNull(Me.cmbStadiu) Then
strWhere = strWhere & " (DOSARE.Stadiu) Like '*" & Me.cmbStadiu & "*'"
End If
DoCmd.Close acForm, "frmPrincipal"
DoCmd.OpenForm "frmRezultateCautare", acNormal
Forms!frmRezultateCautare.RowSource = strSQL & " " & strWhere & "" & strOrder
End Sub
Here: Forms!frmRezultateCautare.RowSource = strSQL & " " & strWhere & "" & strOrder
The error is the following "Application-defined or object-defined error"
Thanks!
View 1 Replies
View Related
Oct 15, 2004
Dim XL As Excel.Application
When I try to run this specific line of code an error occurs. It says:
"User-defined type not defined"
May I know how to solve this problem?
Thanks a lot
View 1 Replies
View Related
Sep 27, 2005
I've put in this in a module many times and this is the first time I have gotten an error.
Dim Conn As ADODB.Connection
When I run the app I am getting a compile error saying "User-defined type not defined".
Anyone have any ideas?
Thanks,
B
View 3 Replies
View Related
Jun 21, 2006
hey,
i have a main form with multiple subforms on it, and a command button for each subform.
when clicked, how do i go about opening the relative subform in its own window with the linking value (histnum) already defined
View 2 Replies
View Related
Sep 2, 2004
I have a combo box that the user can select a value and the form will display that record on the form. This all works fine, but if the user dosn't enter anything in the combo box, it does not work. Is there a way to have it work if the combo box is left empty?
Thanks in advance - John
View 1 Replies
View Related
Nov 17, 2006
hi,
I am new to this forum and to MS Acess. i am not a software engineer or in the field of software. I had to learn as much as i could about MS Acess because of a project i worked on. I have a standalone MSacess database and one of the forms is giving me trouble when I try to enter a new record
The form is called frm_fragrances and has information about a fragrance.
the frm_fragrances has 4 fields in it and a sub form. the sub form has details about the fragrance
In a new record when I enter the 4 fields and attemt to go to the subform which has details about this fragrance I face this error.
an unexpected error haas occurred @2465: application defined or object defined error.
additional information
Active form:<frm_fragrances>
Active control:<txt_VendorName>
Previous control:<frm_fragrances>@Please call the developer for further instructions
I have looked in the FAQ and on this forum for help on error 2465 and cannot find anything to help me. I can work with forms to an extent but cannot write too much code.
I would really appreciate some help in this matter
Thank you
View 4 Replies
View Related
Feb 7, 2008
Hello,
Is there any way of creating a user defined query/ report based on fields in a table? Basically we want a report to be generated based on whatever fields the user enters. So, say we have 30 different fields in a table, the user gets to click on a button that gives them the option to create a report that includes: field1, field2, field3 and where field10 = Blue.
Is this possible to do?
View 1 Replies
View Related
Apr 16, 2008
I want to run an activity query (i.e. objective is to check which data lines have changed) within a user defined date range. Please advise what is the most efficient way to achieve this.
Is it possible to run such a report without having a date column i.e. does Access have a hidden line modification date that can be used in a search?
Look forward to your feedback - thanks!
View 2 Replies
View Related
Jul 18, 2013
I am at the last phase of producing an extensive analysis report. The last calculation I need to the query QryCom_CalculationFinal gives me the "Too many fields defined" error. I have verified that "Ps_Score" calculation push the system into overload, as QryComp_CalculationFinalPrt01 without this calculation works fine.
View 7 Replies
View Related
Oct 29, 2007
Hi All
I have used Allen Browne's code (see http://allenbrowne.com/ser-16.html, bottom of page) as a model for my user-defined type and public function in an events management database.
Using the function in forms/VBA works fine.
But my DB crashes whenever I try to use it in a query.
My modules code:
= = =
Option Compare Database
Public Type EvCheks
EvType As String
EvAttCat As String
EvUnitAss As String
EvCheksAll As String
End Type
= =
Public Function getEvCheks(EV, EvUnit) as EvCheks
'Event Type: Event or DL
If (DLookup("evtype", "tblevents", "[evid] = " & EV)) = "Event" Then
getEvCheks.EvType = "Y"
Else: getEvCheks.EvType = "N"
End If
'Event Attendance Category: INDB= in database or LIST
If (DLookup("evattcat", "tblevents", "[evid] = " & EV)) = "INDB" Then
getEvCheks.EvAttCat = "Y"
Else: getEvCheks.EvAttCat = "N"
End If
'Event Assessing Organisation
Dim AOROName As String
Dim AOName As String
AOROName = DLookup("evunitassessable", "tblevunits", "[evunitid] = " & EvUnit)
AOName = Mid([AOROName], 1, InStr([AOROName], "/") - 1)
Select Case AOName
Case "NA"
getEvCheks.EvUnitAss = "N"
Case "ABCD"
getEvCheks.EvUnitAss = "Y"
Case Else
getEvCheks.EvUnitAss = "X"
End Select
getEvCheks.EvCheksAll = getEvCheks.EvType & getEvCheks.EvAttCat & getEvCheks.EvUnitAss
End Function
====
On a form, I have a button with on-click event code that defines the EV and EVU variables and displays a message box
MsgBox getEvCheks(EV, EVU).EvCheksAll
This works fine.
=====
In a query I have this SQL code:
SELECT tblEvUnits.EvId, tblEvUnits.EvUnitID, getevcheks([evid],[evunitid]) AS EventDetails
FROM tblEvUnits;
This causes the db to crash.
I cannot get the "back-end" definition of the function into the query, as Access rejects it because of the dot, i.e. ".EvCheksAll".
I suspect there is some formatting quirk for using a public function that includes a user-defined type, in a query.
Any/all assistance much appreciated.
Regards
AlanM
View 3 Replies
View Related
Feb 8, 2008
I want to extract the integer part of a string data as a single field.
How to apply self-defined function in Query Design Mode?
any hint is appreciated, thank you in advance~
View 1 Replies
View Related
Nov 17, 2004
Hi all,
I have quite a large select query that pulls in Date values from different tables. The end result is a list of records, where each record has 7 types of dates, put in columns A, B, C, D, E, F, G. What I need to do is, for each record to compare dates in those columns and put the LATEST one of them in the column MDATE.
I've tried using IIF function, but it grows exponentially with the number of values involved, and it can only handle 5 values before its cause error "too large". Alternatively I thought I could write a function to return the max date and call it from within the query, something along the lines of "select *, fnMaxDate(A, B, C, D, E, F, G) as MDATE from tblBLAH etc". The function is:
----------------------
Public Function fnMaxDate(a As Date, b As Date, c As Date, d As Date, e As Date, f As Date, g As Date) As Date
Dim Temp As Date
Temp = Nz(a, "01/01/1901")
If Nz(b, "01/01/1901") > Temp Then Temp = b
If Nz(c, "01/01/1901") > Temp Then Temp = c
If Nz(d, "01/01/1901") > Temp Then Temp = d
If Nz(e, "01/01/1901") > Temp Then Temp = e
If Nz(f, "01/01/1901") > Temp Then Temp = f
If Nz(g, "01/01/1901") > Temp Then Temp = g
fnMaxDate = Temp
End Function
----------------------
I've put a break at the start of the function, to see what it'll come up with but when running the query it didn't appear to call it. All values in MDATE where #error.
Can someone advise me, please, on the best way of picking the LATEST date? I'd also appreciate it if you tell me why the function didn't get called, as I was quite certain it is possible to call user defined functions from within the query.
Cheers,
Kat
View 1 Replies
View Related
Aug 19, 2013
Is there a way to retrieve the value of a user-defined type in a query?
Here's the type:
Code:
Public Type ClassRank
Rank As Integer
ClassCount As Integer
End Type
I have a function with the following excerpt:
Code:
Function GetRank(strDOD) as ClassRank
...
GetRank.Rank = intRank
GetRank.ClassCount = intCount
...
End Function
In my query I expected to be able to put the following:
Code:
GetRank(strDOD).Rank & " " & GetRank(strDOD).ClassCount
However, Access didn't like the period in .Rank or .ClassCount.
Should I just write two different function to get 'rank' and 'classcount'?
View 2 Replies
View Related
Dec 9, 2013
Any way to make the query expired after a defined period but it must remain with that query only not for other queries. Also the query expires should have an option to be alive again or redated for next period after updation means expiry means stop working but not corruption or anything else.
View 5 Replies
View Related
Jul 24, 2013
I am making a classic sales over time crosstab query.
Rows: Customers
Columns: Sale months
Sales date is defined by the ETD of the order.
However, with the simple Format([ETD],"yyyy-mm") I get regular months, but I need to adjust the months to be between the 21st and 20th rather than 1st to 31st(30th).
August would be 7/21/2013 to 8/20/2013
September 8/21/2013 to 9/20/2013
Is it possible to format the columns this way?
View 2 Replies
View Related
Sep 4, 2013
I got a field which is defnined based on a query result to ease the user input. However, some input are not in the query list, if I input data directly to the field, ACCESS complained I must choose item from the query result. Is there any way that the user can either select from query result or direct input to that single field?
View 1 Replies
View Related
Jul 21, 2005
http://lemmikki.uw.hu/promotion.JPG
Okay, this is how my form looks. I have thousands of records, but in a special way. I would like to make a search having the email, when and what together. The table is like this:
email1@xyz.com 20050712 E0001
email2@xyz.com 20050712 E0001
email3@xyz.com 20050712 E0001
email2@xyz.com 20050712 E0002
email3@xyz.com 20050712 E0002
email1@xyz.com 20050720 E0003
email2@xyz.com 20050720 E0003
email3@xyz.com 20050720 E0003
email4@xyz.com 20050720 E0003
email1@xyz.com 20050720 E0004
email4@xyz.com 20050720 E0004
Okay, there's a key for finding the records, but searching by it is quite difficult. Searching by these 3 data would make it easier. But it's quite difficult for me. I would like to have 3 lists (on the picture I have 2 so far) and a button that I can press and the record would come out...
Can someone help me?
View 4 Replies
View Related
Dec 20, 2005
Hi,
On my form i have a text box (txt_search) and a search button (cmd_search). Now when the button is pressed i want it to go to another form (that bit is done) and display the search results (the bit that is hard).
How can i get it so that when i click the button. It searches some predefined fields with the text in the txt_search once the button is clicked.
Thanks
k0r54
View 1 Replies
View Related
Jul 26, 2006
I was looking for help to find out how i would put a search facility on a form
Ive written a query with a parameter where you input a persons name in order to find it in a table
the porblem is when i base a from on this query it brings up the parameter asking me to enter the name, the query works fine it does what i want it to do.
i want to be able to open the form and if i need the facility to enter a name an d bring the details up in a sub form
any ideas?
View 2 Replies
View Related
Jun 11, 2013
I have used one of the members' code which was posted in a tread before to create a search box in a form. This code works perfectly when the form itself is open. Otherwise, when I put this searching form along with other forms in one navigation form, I get the following error: "Enter Parameter Value: Forms!FRM_SearchMulti!SrchText"...The code that was used for the Search box is as following:
Private Sub SearchFor_Change()
'Create a string (text) variable
Dim vSearchString As String
'Populate the string variable with the text entered in the Text Box SearchFor
vSearchString = SearchFor.Text
[code]....
View 7 Replies
View Related
Oct 1, 2005
I have a database and I made a query to search records. The query worked perfectly, but now the only problem is that if a search has an ID number that is bigger than 239, the results will not be displayed. The query will be empty! :S
What is the problem with my query?
Thanks in advance
Aber^28
View 2 Replies
View Related
