I have a combo box that displays LastName FirstName.
If I have two people with the same lastname it finds the first one in the table w/ that last name. Please help with my code so it searches by firstname and last name. Thanks in advance.
Kacy
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[Contact_LastName] = '" & Me![cboContact] & "'"
Me.Bookmark = Me.RecordsetClone.Bookmark
Hi
I have a search form with 1 combo box and a button which when the user inputs an invoice number and clicks the button, a main form is opened for that particular record.
The problem I have is to display an error message before the main form opens if the user has either left the combo box blank or they have input an invoice number that doesn't exist.
This feels like it should be easy......!
Thanks for any help
Georgina
I got this function which is supposed to find a record based on the primary key, and then give three fields a value if they should be empty.
Public Function DoesXrefExist(faPersonID As Long, faRoleCode As String, faDorpID As Long, faDorpCode As String)
If (DLookup("PersonID", "PEOPLE", "PersonID =" & faPersonID) > 0) Then
End Function
Thats as far as i have got...for the next part.I get the feeling that i need to use the recordset object that exists somewhere.How do i access that recordset object? I have not been using any DAO or ADO, just straight VBA. I know i can create recordsets and stuff using DAO/ADO, but there must be a recordset that has been created already by the Dlookup command...
I currently have a form to manage inventory. On the left side of the form, I have a list box that shows all of the items in the database and finds the record on the form based on the selection.
I would like to have toggle boxes or something underneath the list box that when clicked will sort the box by vendor and item and another that when clicked will sort the list by part number and then select the record on the form based on the selection at that time.
I have no idea how to accomplish this...can anyone help?
I am trying to add a combo box into a form and when the Combo Box wizard pops up I only get two choices:
I want combo box to get values from another table or query. I will type in the values I want
I want the third option...
Find a record on my form based on the value I selected in my combo box.
Why am I not getting the third option??
Can someone tell me how I migh find a record in a main form based a a selection in my subform?
I have a Main form called frm_ProductionSchedule.
It contains three subforms:
frm_ProductionSchedule_subform
frm_ProductionSchedule2_subform
frm_ProductionSchedule3_subform
I would like to be able to select a record within any of the subforms and have the main form display it. The main form is linked to a SQL server Table. The subform are based on queries. The unique PK field is a date field called record_date.
I should add that the Link Child/Master fields are blank for all Subforms. Each Subform is linked to a query and displays data for a particular machine (1,2 or 3) AND is updated based on a combo selection of the Production week. The queries for one subform would look something like this:
SELECT dbo_tbl_ProdSchedule.PartType, dbo_tbl_ProdSchedule.PartNumber, dbo_tbl_ProdSchedule.Work_order_no, dbo_tbl_ProdSchedule.FrameType, dbo_tbl_ProdSchedule.FrameNumber, dbo_tbl_ProdSchedule.Shift, dbo_tbl_ProdSchedule.Sch1_date, dbo_tbl_ProdSchedule.Sch1_spins, dbo_tbl_ProdSchedule.Sch1_sheets, dbo_tbl_ProdSchedule.Sch2_spins, dbo_tbl_ProdSchedule.Sch2_sheets, dbo_tbl_ProdSchedule.Sch3_spins, dbo_tbl_ProdSchedule.Sch3_sheets, dbo_tbl_ProdSchedule.Sch4_spins, dbo_tbl_ProdSchedule.Sch4_sheets, dbo_tbl_ProdSchedule.Sch5_spins, dbo_tbl_ProdSchedule.Sch5_sheets, dbo_tbl_ProdSchedule.Sch6_spins, dbo_tbl_ProdSchedule.Sch6_sheets, dbo_tbl_ProdSchedule.Sch7_spins, dbo_tbl_ProdSchedule.Sch7_sheets, dbo_tbl_ProdSchedule.Frames_due_date, dbo_tbl_ProdSchedule.Comments, dbo_tbl_ProdSchedule.Record_date
FROM dbo_tbl_ProdSchedule
WHERE (((dbo_tbl_ProdSchedule.Sch1_date)=[Forms]![frm_ProductionSchedule]![cmbSchDisDate]) AND ((dbo_tbl_ProdSchedule.SpinCoaterNo)=1));
AND WHERE ((dbo_tbl_ProdSchedule.SpinCoaterNo)=2)), etc. Respectively for each subform.
Thanks in Advance!!
John
I'm trying to find a subform record from a separate pop-up form based on its ContractID. When I click the button, I get the following error:
"A macro set to one of the current field's properties failed because of an error in a FindRecord action argument."
When I step through the code, it works just fine. Here is my code:
Code:
'Find contract
With Forms!frmContractForm.frmContractSub
.Form.ContractID.Enabled = True
.SetFocus
.Form.ContractID.SetFocus
DoCmd.FindRecord Me.ContractID
.Form.Business.SetFocus
.Form.ContractID.Enabled = False
End With
I have a database which has a main form and subform built in linked by parent/child customerid, what i would like to do is search all the subform records from the whole DB and return its parent record on the main form?
Can this be done? because if i use find it will only search the filtered form i have onload of the form?
My onload event is based on fosusername()
Ms Access 2003. Is there any way to find out what is using a certain query?
I need to know if qryPFP is used by other reports or forms before changing it. I don't want to mess up anything else somewhere else. Does this make sense? We have a LOT of forms, queries and reports and it's hard to plow through everything.
I have a form with a dropdown box that shows the 'Group by' records in a table.
For example the form shows "Art_6". My table has many records (could be 1000's) that start "Art_6".
I want a macro/query to find all records that start "Art_6" and delete them.
How to find only those that start as described.
I have a price table:
Code:
tblPrice
PosNr PriceDate Company Price
1 01.01.2014 Firma A 5
2 02.01.2014 Firma A 7
3 03.01.2014 Firma A 9
4 04.01.2014 Firma A 8
5 06.01.2014 Firma A 6
6 02.01.2014 Firma XY 11
7 03.01.2014 Firma XY 9
8 04.01.2014 Firma XY 7
9 05.01.2014 Firma XY 8
10 06.01.2014 Firma XY 10
And I have a table with the dates, for which I need a price.
Code:
tblDates
PosNr PriceDate Company
1 01.01.2014 Firma A
2 02.01.2014 Firma A
3 03.01.2014 Firma A
4 04.01.2014 Firma A
5 05.01.2014 Firma A (no price available)
6 06.01.2014 Firma A
7 02.01.2014 Firma XY
8 03.01.2014 Firma XY
9 04.01.2014 Firma XY
10 05.01.2014 Firma XY
11 06.01.2014 Firma XY
And now I want to combine this tables, and for the dates which have no price, the last price should be taken.
Code:
tblResult
PosNr PriceDate Company Price
1 01.01.2014 Firma A 5
2 02.01.2014 Firma A 7
3 03.01.2014 Firma A 9
4 04.01.2014 Firma A 8
5 05.01.2014 Firma A 8 (actualy no priceavailable, so take last price)
6 06.01.2014 Firma A 6
7 02.01.2014 Firma XY 11
8 03.01.2014 Firma XY 9
9 04.01.2014 Firma XY 7
10 05.01.2014 Firma XY 8
11 06.01.2014 Firma XY 10
how I can get this?
I have this code, but it need hours.
Code:
SELECT tblDates.PosNr, tblDates.Company, tblDates.PriceDate, (SELECT TOP 1
B.Price
FROM
tblPrices As B
WHERE
B.Company = tblDates.Company
AND
B.PriceDate <= tblDates.PriceDate
ORDER BY
B.PriceDates DESC ) AS Price
FROM tblPrices RIGHT JOIN tblDates ON (tblPrices.PriceDate = tblDates.PriceDates) AND (tblPrices.Company = tblDates.Company);
This may not even be possible, but I am looking to create a Query that can locate records in an Access Table based on 2 columns of data that I have stored in a CSV file.
My table contains several fields, 2 of which are "Dept" and "SKU" and has over a million records.
My CSV file contains 3 fields: "Dept", "SKU" and "Total" - total being the number of times that particular Dept/SKU combination is used.
I need to be able to parse the dept/sku values from each row in the CSV to the query and locate only the records that contain the same values in the Access table.
The plan being to delete out those that are identified by the query.
I am 2 years into my database. I am trying to find and open a folder based on a text box. The problem is folder could be in many sub folders which is hold on our J: drive.
To further complicate, the folder i am searching may not be exactly as the text box states.
EG. Text box could say 123456 however the folder could be called M123456 etc.
How do i locate a folder or subfolder and open it based on part of a text box...
Hello to all,
Little new in Access i would like to know how to use the key to find directly to a record in a table.
ie : i have a table where the primary key (unique) is a date, using VBA i would like to know the instruction to find directly the record 01/06/2004 for exemple.
Thanks in advance.
VINCENT
I forgot to mention that my form is based upon a table, i have created other tables and forms based on this one therefore I don't want to change a lot of stuff. I tried the forum but nothing helped.
I have a form with about 10 fields, i added a find button to search for a record, using three fields, when this button is click i want it to display all the info pertaining to the search criteria, (this will be displayed on the same form populating in the form which contains other feilds) or a error saying record not found.
Thank You
Hi, i am a beginner to access and need some help if someone would be kind enough...
i have a table (tblCustomer) which has all the standard customer details such as name, address, phone etc. The primary key of the table is a autonumber field (Customer ID). i have autoformed it so all the fields are now on a blank form. I want to add a text box to the form so that i can find a record by typing the customer's surname. Once this has been searched, i would like the records to be listed in a table in a subform further down the original form. I would then like the user to select the correct record they are looking for from the sub form, and the details to come up in the main form so they can be edited. The reason for the whole sub form part is because there may be more than one customer with the same surname.
please could someone suggest how to achieve this.
thanks
Hi, i am a beginner to access and need some help if someone would be kind enough...
i have a table (tblCustomer) which has all the standard customer details such as name, address, phone etc. The primary key of the table is a autonumber field (Customer ID). i have autoformed it so all the fields are now on a blank form. I want to add a text box to the form so that i can find a record by typing the customer's surname. Once this has been searched, i would like the records to be listed in a table in a subform further down the original form. I would then like the user to select the correct record they are looking for from the sub form, and the details to come up in the main form so they can be edited. The reason for the whole sub form part is because there may be more than one customer with the same surname.
please could someone suggest how to achieve this.
thanks
I have navigation between forms and when a user navigations from the main form(candidate) to another form(1) and then back there is only 1 record displayed on the candidate form as it is only bringing back the record tied in by the application number.
So if you then want to search for another candidate it doesn't work because the candidate form is filtered.
I've tried using this in my find function but it doesn't work: DoCmd.GoToRecord , , acFirst
Can anyone help?
Thanks,
Dan
The strSql string should find the record that I have already saved in my admin table and then compare it with combusername, where is my mistake.
Private Sub Command7_Click()
'Dim db As Database
Dim strSql As String
'Set db = CurrentDb()
strSql = "Select adminpass From admin"
If ((Nz(txtpassword, "") = "") Or (Nz(combusername, "") = "")) Then
MsgBox "Please Enter A Valid Access", vbOKOnly, "Error"
ElseIf (txtpassword = "admin" And combusername = strSql) Then
DoCmd.Close acForm, "Secure"
DoCmd.OpenForm "Home"
ElseIf (txtpassword = "user" And combusername = "user") Then
DoCmd.Close acForm, "Secure"
DoCmd.OpenForm "Home"
Else
MsgBox "Wrong Password, Please Try Again", vbOKOnly, "Error"
combusername = ""
txtpassword = ""
combusername.SetFocus
End If
End Sub
I have 12 fields, each containing a number. I want to display the last non zero value in a query. I've looked at "last" but this returns the items in chronological order, which is not neccessarily the order my data is entered in.
eg.
fields: 1 2 3 4 5 6 7 8 9 10
data: 0 7 3 0 0 5 4 0 0 0
I would want the query to return 4, even if the last value entered was the 7.
To make it more complicated, I have many rows of data for each set I am using, and have to aggregate these based on a key column
eg.
Fields: key 1 2 3 4 5 6
Data: 1 0 0 0 2 0 1
1 0 0 0 3 0 3
1 6 5 4 3 2 1
2 1 2 3 4 5 6
2 0 0 0 0 0 0
Any tips?
Cheers
Kev
I have a form with a form header section that contains a combo box and a text box. The form also has a detail section that has a tab control that contains the rest of the data entry boxes. I put a Find Record button in the detail section of my form. When I try to Find Record using the combobox or textbox values in the form header, I get an error saying it cant search the tab control, use the Gotocontrol action before the Find Record Action, change current field action from yes to no, or change the focus to a searchable control. If I search items in the detail section I do not get errors. The code created by the Find Record button is below. I'm not sure how to get it to set focus to the appropriate box in the form header section if that is the field I am searching on.
Private Sub cmdFind_Click()
On Error GoTo Err_cmdFind_Click
Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_cmdFind_Click:
Exit Sub
Err_cmdFind_Click:
MsgBox Err.Description
Resume Exit_cmdFind_Click
Hello,
I have a large database I set up and we have been using fine for a couple of years now.
All of the sudden, anytime Find Record is clicked and the value is entered, I am getting the following error, on ALL forms. Nothing has been changed in the DB itself and it worked GREAT before.
The control 'Command 138' the macro is attempting to search can't be searched.
Try one of the following:
* Add a GoToControl before the FindRecord action.
* For the FindRecord action, change the Only Current Field action argument from yes to no.
* Change the focus to a searchable control.
Can anyone help? It is VERY frusterating and I can't figure it out.
Thanks so much! ;)
I've enclosed a screenshot as it tells the story pretty well by istelf.
I would simply like to be able to click any item in the listbox (populated from a query simply showing all records in a table). Once an item is clicked in the listbox, I'd like the form to go to that record (this is to enable quick finding and deleting of records).
I've been playing for a while, and can't figure out how to make Access go to the record that's selected in the listbox.
Thanks for any help.
Steve
On one of my forms I have a command button to find a record. The Find button is supposed to locate a record based on it's job number, this is not a primary key. I have this feeling it has something to do with the field Job Number because it is like this throughout the db (it is only recognizing the first record.) I even tried putting in another record but it still only recognizes that one record. What did I do wrong?
learnasugo
Hi,
Newbie problem. I want to display on a form the last entry or (record) from a particular field of a table
(the date field of the last club event). I have set the Form Property of 'Data Entry' to 'Yes' to prevent the user from
having to scroll through records to get to the next blank record. Could someone kindly give me a clue how to go about
displaying that last record of the field. Thank you.
I have developped a table and some forms reated to that.Since the main survey is quite long, I developed 4 forms to enter the data. Now I have a question related to data entry.Suppose I start entering data for one record and I decide not to fill up all the 4 forms, I simply decide to go back to it later. How can I go to the same record and keep entering the data from where I left?
If I am in Datasheet view, I can easily use the Find option to look for the record. However, I dont seem to be able to understand how I can open the form of that record to enter the remaining data.