I have a form that has a control to search the table for a specific record See code below that is on the AfterUpdate event of a Combo Box
Code:
Set rs = Me.Recordset.Clone
rs.FindFirst "[ClientID] = " & Str(Nz(Me![cboName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
[Code]....
What I need to know is how to make the rowsource based on the filter and not the table.
I am trying to allow a calendar to search through a text box with a large amount of data in which will have headings using the date so i wanted to use a calendar search. Hopefully this will be be like the find function in word where you type (or in this case select a date) then click search or next which will take you to that specific date.
View 14 Replies View RelatedAccess 2010. I have a form pulling from a query to create a "To Do' list of sorts. On this form is a button to open an input form for the corresponding record (I hope). When this button is used I want it to pull certain data for that specific line from the query and input it into the new record opened by the button. I know this is possible as I use another db that does this but I have not been able to figure out how to make it work in the new db.
View 14 Replies View RelatedHow to let the user save a preliminary version of a record in a form? I have a table with records representing airplane status. Sometimes, the user wants to save a preliminary version of the status and create some other versions and save all of them for that specific airplane, but this does not happen for all the airplanes. How would I do that? Is it possible at all?
View 1 Replies View RelatedI want to hyperlink from a query direct to the relevant record in a specific form. I have a hyperlink field in the form which shows up in the query. When clicked in the query, this hyperlinks to the form but I cannot make it select the correct record in the form.How do I get it to select the correct record?
View 3 Replies View RelatedIf there a way to set focus to a specific page from a command button on a Tab Control?
I tried Me.TabCtl0.Value = 3.
But it doesn't work.
I have a form based on a query in datasheet view. What i would like is to show only those records that fulfill the truepart of iif statment, and do not show the ones that fulfill the false part.
I have the following fileds in the query
month
currency 1 checkbox
currency 2 checkbox
sum
In the form, the control named month contains the following code:
control source: =IIf([checkbox1]=no;[month];0)
So this gives the appropriate months, but also shows the all other records with a 0 in the month field. I would like to get only the records shown that are correct with the truepart of the statement.
I have a search form that's slightly modified search form modeled after the one available here on this site. It's a main form with a list box. I use the listbox to narrow down the results I want from the search function.
The search details are shown in a sub form that contains sub-sub forms
As seen in the code below, I am using the listbox to set a filter on the subform . Works famously until I search for something that does not exist in the database. I then receive runtime error 3075.
I believe it's related to the way I'm showing the details in the subform (by enabling a filter). If there is no record in the database that match the search criteria, I don't want any error messages or pop-ups and preferably would like the listbox to be blank.
Attached a screenshot of my form.
red = main form
green = subform
blue = sub-subforms
tan = obscuring the search results
Code:
Private Sub SearchFor_Change()
Dim vSearchString As String
vSearchString = SearchFor.Tex
SrchText.Value = vSearchString
Me.SearchResults.Requery
[Code] .....
So I'm not new to Access but I am to 2010. It has been a bit "challenging". Here's my first question:
1.) I'm trying to search on a field by using a command button. I basically want to click the button and the following message pops up: Enter MRN.
2.) When the MRN is entered, I would like the form to filter on all records that have this MRN.
3.) In old versions of Access, I would create a Macro for this and then call the Macro in the form.
4.) I've tried the FindRecord action in the Macro but it does not work. I actually came across several actions that don't seem to be working properly (getting error messages).
5.) In my head, this should be one of the EASIEST things to do. I've done this before in several different databases. I will admit it has been a few years since I have used Access for this (ie. building forms, macros etc.). I've primarly used it to pull in a data set and then run some queries to get the data I need quickly versus using Excel.
I have 5 textboxes and a multiline textbox in a form in my Access Database. I am going to type several keywords in those textboxes and I am going to type an article in the multiline textbox.
What I need to do is I want to calculate the keyword density in the article. I can do that if i know how to search the whole text in the article and count the specific word. And I want a function to count all the words in the article too.
I currently have a database with a few search forms. I recently attempted to add a box on one of my search forms to search 3 cells of a record for a key word. What happens is when I go to search, say for P0442, it does not bring up all of the records that contain that keyword in the 3 cells I have outlined. If I step down the code in the OR boxes of the query, it seems to work better but then for some reason my other search criteria , (Tracking number etc) does not work. I will upload the database for reference. I am currently working on the Search(View Only) and that is where you will find the query to work on.
View 3 Replies View Relatedhow to read a specific line in a CSV file (using VBA), to see if the phrase "There are no records available." is present.
If it is present, then I'm going to do a debug.print stating that there are no records to load - and then the script will move on to the next file. If the phrase isn't present, then I'm going to upload the file to Access, parse the information, and then upload it to a CRM. (I already have the latter portion of the code up and running....I just need to account for the first part, where I can determine if the file has data or not).
The structure of the file never changes. The first row is composed of eight column headers (Post Date, Card Number, Card Type, Auth Date, Batch Date, Reference Number, Reason, Amount) and (if) the phrase "There are no records available." is present, it will show up on the second row, in the first column (under Post Date).
I am new to access i have a problem which is i have made a form which contains a subform and a read only subreport, what i want is the ability to select a record in read only subreport as in the picture attached and make a button that when i press on it, it should copy the values of the itemsID field, Packing field, ContainerNo field and origin field from the selected record and then paste them in the subform below.
Also i want to add more then one item, so the when i press on another record it should paste the values below the first record.
I would like to create a form that allows me to search for a folder on my desk top, then once located i can transfer that file to a specific location on another drive, Similar to the Browse / upload function you see on many applications.I am using Access 2003. Is this possible??
View 5 Replies View RelatedI have a access 2003 database, and i am wondering if i can't setup a search function in a form that will open a file in windows explorer?
What i would like to do is have a test box that i enter in a number for example: 1234 then have a button named "Search" hit that button and it opens a corresponding file in windows explorer named 1234, or just opens that folder directly..
I have a continuous form for which the recordsource is a query that retrieves dates from 10 days in the past to 10 days in the present. I want the record with today's date to be at the top of the form. The record with the oldest date is always on top. Is this a scrolling issue? How can I get the record with today's date to appear on top?
View 5 Replies View RelatedI have a problem with my database I have a combo box that will search for my record. Actually its working I input the specific number it goes to the specific record. But I want, if there no existing record in my database it will display a Messagebox that "No record Found" I try to put a code in a macro builder in a after update property field but nothing happened.
Expression code that it will display the msgbox if there's no record found.
the given code from macro builder is attached. I try to have an if else statement but I dont know how to not equal that giver conditional expression.
I need to export a specific record in the table. The menu's export option only export the entire table. Is there a way to define certain record to export?
Thanks
Well, here's another newbie question.
First let me say, I've search this forum for as long as tolerable looking for a simular problem as mine and could not find one. I am sure this isn't the first time this question has been asked.
I have taken a few Access courses at a local college for my own personal use and have discovered that the courses albeit expensive, just covered what appears to be mere basics.
At this time, I do not understand SQL or VBA. Nonetheless, I have created a database to organize my four bookcase library.
The library consists of books & mags, records(33's, 45's and 78's), DVD's, VHS & Digital tapes, CD's(Music and software).
I have created tables for Books/Mags, Video and Music. All of which pulls required data from a list in other tables such as mediatype (Book, DVD, Record etc..), location(C1S2R which has a description field as Case 1 Second Shelf Right side area) and Genre(Country Music, Video Sci-Fi, Fiction Reading etc...).
When I query on a field such as Title, it brings back the desired results. However, when I query on a field where the data was import from another table such as Genre, the result is an empty table and yet the record truly exsist.
For instance this is the criteria I used Like ["Genre"] & "*" When prompted I typed V and expect at least one record that had Video Sci-FI listed. The result came back blank. When I just hit OK then the whole table would display and sure enough the Video Sci-Fi record would be there as well.
This is true of all the fields/data that has been pulled into the table from another table. I have tried many combinations of the criteria command to no avail.
If I can get this part to work I can quit biting my nails (or should I say nubs)
Thanx in advance mates.
~Anobody~
~a nobody today a somebody tomorrow~
I have a main form and a nested subform. The main form is based on customer details and displays customer name and the customer primary key (custID). When this form is opened i pass two parameters to it, BPID and SIID. A BP is a business picture and a SI is a sales interaction. These are in a one to many relationship.
The subform displays a sales interactions (si) of a business picture (bP).The subform is set up with master child links of custid;bpid. This restricts the subform to only showing records for the a specific customer and specific BP (the user can cycle through all the linked SI's using the record selectors.
When the main form is opened I want the subform to display the sales interaction the user has selected when opening the main form based on the passed SIID but cannot get this to work. At the moment the subform just displays the first record.
I want to autofill in a text box with reference to a table I have in Access.I have the first text box that returns the first record in the table i want to get the data for with "text1 = Dlookup("numplate", "cars", "")
This returns the first record. I want the second text box to return the second number plate from the table.How can I get the second record to be returned...?
Is it possible to use VBA or macros to open a table to a specific record?
I know how to open a table in database view using a command button. I'd like to have it open to or automatically scroll to and select a specific record, based on the content of the form. This way I don't have to spend time scrolling through the whole table to try and find the desired record.
I'm very new to access database and I'm trying to create a macro that allows the user to enter data after seeing a mistake in a form. I need a macro that will open the specific table and record of a piece of data. For example there is a student TestName3 whose grade on a competency is 30% but should really be 45%. I want a macro by the competency percentage to allow the teacher to edit that without looking through the entire table. The macro should prompt first to ask if the teacher is sure they would like to edit, second prompt asking for the student's unique ID number, and finally be taken to the specific record and table related to that competency.
I have tried to use a vastly overcomplicated DLookup and Order column to give me the value for a acGoTo search.
Here's what I have.
Option Compare Database
Dim answer As String
Dim response As Object
Dim gotoresponse As Object
Private Sub Command71_Click()
On Error GoTo Command71_Click_Err
[Code] ......
I have 2 Tables. Table "BOL" and Table "Containers". Both contain "Job_Number" and "Bill_of_Lading_Number" as a field. I would like to get a "Bill_of_Lading_Number" record from "BOL" and add it to "Bill_of_Lading_Number" in "Containers" table based on condition when both "Job_Number" are equal. I have tried two methods. One with DLookup and another with SELECT. Both work but with a small problem.
When I execute them, a dialogue box popup asking me to enter the "Bill_of_Lading_Number" value while the required value shows as dialogue box heading. This box shouldn't come up and I am not sure why this is happening. "varsJobCont" variable I get from another form and is "Job_Number" value that determines the record to be selected.
My Both Codes are:
Dim strBill As String
strBill = DLookup("Bill_of_Lading_Number", "BOL", "Job_Number = " & Application.TempVars("varsJobCont").Value)
DoCmd.RunSQL "Update Containers SET Bill_of_Lading_Number = " & strBill & " WHERE Job_Number = " & Application.TempVars("varsJobCont").Value
Dim strBill As String
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = CurrentDb
[code]....
shows the dialogue box and the heading "bbb1055" is actually the correct value I needed to get. It should have been put in the other table automatically without the need to insert it manually again.
I have a form where a user enter an ID and some details. This is saved in Table A.The ID is also in Table B - What I want is, once the user saves it in Table A-I want it to be deleted from Table B. Ultimately, Table A has all information and Table B will only have those IDs that haven't been entered in Table A. what is entered in Table A shouldn't be there in Table B.Is there a way to do this using a macro?I did find a VBA code online -
Private Sub Submit_Enter()
DoCmd.RunSQL "DELETE * FROM TBL_ClaimsToBeAssigned"
where [Claim ID] = Forms![FRM_PendsAssign]![Claim ID]
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM TBL_ClaimsToBeAssigned"
DoCmd.SetWarnings True
End Sub
I've done this dozen's of time - but it doesn't seem to be working now. I have a continuous form (frmHome) where i want a user to be able to click on a record, and be able to open up that specific record in "frmCustomer". The key field here is pkCustomerID (i have it in the query).
frmHome is run off a query ("qryCustomer").
frmCustomer has "tblCustomer" as the source.
The code i've used on a button on "frmHome" is:
DoCmd.OpenForm "frmCustomer", , , "[pkCustomerID]=" & Me!pkCustomerID
It always only open Record #1.