I am trying to locate a field in the form. The field is existing in the Tab Order list. But when I use the tab to locate the field in design or run mod, the tab never jump to that particular field.
Can anyone tell me how I can locate the field (in order to change the properties of this field)?
Given the firmname and textbox name is it possible to programmically get the fieldname and table name where the data for that control is held.
I can use .controlsource and .recordsource
But is possible that .controlsouce is an alias of the actual fieldname.
Similarly the recordsource could be a query, from that I want to get the actual table, complicated say if two tables in the query had a field with the same name (even if only one was referenced)
Thanks.
I have a form that is bound to a query. The user inputs their id in a text box and the form should find the corresponding record with 5 different pieces of data displayed in separate text boxes. There is at any given time only one record set per id. The query has in the EmpID the criteria: [Forms]![frmProdStopEntryNew]![EmpID] to reference back the EmpID as the filter.
On the form, after the employee enters their ID, the afterupdate should pull-up the record that corresponds to that id. The code for the afterupate is:
Code:
Private Sub Emp_ID_AfterUpdate()
Me.Filter = "EmpID = '" & Me.Emp_ID & "'"
DoCmd.RunCommand acCmdApplyFilterSort
Me.StopTime = Now()
ReasonID.SetFocus
End Sub
I also have a form load to go to a new record just so that the form is blank when the user enters it.
Code:
Private Sub Form_Load()
On Error GoTo Form_Load_Err
DoCmd.SetWarnings False
DoCmd.GoToRecord , "", acNewRec
Form_Load_Exit:
Exit Sub
Form_Load_Err:
MsgBox Error$
Resume Form_Load_Exit
End Sub
The problem I'm experience is that the query to find the record has a 50/50 hit and miss success rate. I think this is because the form load gets activated each time the query is refreshed afterupdate. And the form load has the new record syntax so it sort of nullifies it each time. How do I fix that?
I am desinging a report, I cannot see some fileds in Sorting and grouping. The query that I am runing has those fieds, How can I get to see all the fileds in sorting and grouping?
Thanks
When opening a form i get asked for a parameter value for a field that has been deleted. it obvioulsy still exists somewhere but I cannot find it. i have analysed performance, all tables, forms and queries but it doesn't come up with this filed anywhere. the field name has even been spelt wrong so i thought it would be easy to track! Help - what else can i do to find where this parameter is being called from??
View 4 Replies View RelatedI'm trying to convince Access to find a value and then convert said value into the name of the field it came from.
Below is my current code:
Code:
LowestMark:
Minimum([AvgOfOpening1],[AvgOfConfidentiality1],[AvgOfEmail1],[AvgOfTenureStatement1],[AvgOfOwnership1],[AvgOfClearExplanation],[AvgOfProbing1],[AvgOfUseofTools1],[AvgOfResolveBeforeS2S1],[AvgOfBridgeSolutions],[AvgOfSummarizeWrapUp1],[AvgOfOfferFurtherAssistance1],[AvgOfOneFaceOneVoice1],[AvgOfAppreciateBrandCox1],[AvgOfInteractwithCustomer1])
(Minimum is from a module that finds the lowest value in the list.)
I need my query to tell me which field gives that value.
Example:
Agent Name AvgOfEmail1
instead of:
Agent Name 41.7%
how to make this work?
I am OK with a number field as I can put it in Excel and use =Mode().
But that wont do text such as lastname, suburb etc. I can do suburb by doing postcode but no such equivalent from some others I want to do
i want to find out all the tables that contain a particular field. eg.employee_ID exists in employee table and other tables, how i can find out what are the other tables? can i run a search query to do this?
View 2 Replies View RelatedI'm using a filter on the form load of one of my forms that potentially would have 3000 records. The idea is to speed the process up a little.
I'm using:
If IsNull(Forms!frmProjectMain!ProjectID) = False Then
DoCmd.GoToRecord , , acFirst
end if
Me.FilterOn = True
Which works fine. However, I'd rather it was the ID with the lowest numeric value that was displayed and filtered rather than the last record chosen. i.e. 1 instead of a random number.
Does anyone know how i'd go about writing code that would tell access to go to the lowest ID to filter?
thanks for any help I receive here
I'm lost on how to use the LIKE to find a value in a field.
I try to use the open folder if a field contains "Retail" or any other containing as a part. So, I could have just "Retail" "New Retail" and then act on it.
How can I use the below proper?
Code:
If Me.Business_Type Like "*" & "Retail" Then
I've inherited a database with many queries (most based on a single table). There is a requirement to change the name of a few fields in the table. Is there a way to find ALL queries/reports that are utilizing the fields that need to be changed so I know what queries need to be modified?
View 3 Replies View RelatedCan anyone help me in creating a field from a query which will pull the first word only from a specific field.
Thanks.
I have a date/time format field.
"8/21/2007 10:02:34 PM" is what my column shows.
What would I add to the criteria to the query to find all records added today?
at the end of the day my user needs to run a query that will show everything from today. Thanks.
Ok Im having trouble with this, I need to remove cities from an address field but don't know a simple query that does this. Can any body help
heres an example
Address1 City
10/F, HONG KONG LAI CHI KOK EXCHANGE IIHONG KONG
Now I want to be able to pull Hong kong out of the address1 field but without searching in the address field with *HONG KONG* as this would involve checking every city in a table.
Please help!
Code:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Complaint", dbOpenDynaset)
[Code] ....
I find the record, and then try to update a field. The drop down menu field gets updated after it is initially changed from blank to a value, but it never changes after that though the re!C02 field has the correct newer data when I change it again.
Hello all,
I am sure this must be simple, but I can't get my head around it.
I have a query that looks at client bank account numbers, and finds duplicates.
There are a LOT of duplicates, because we have several accounts for the same person.
What I would like to do is find all the records where the account number and sort code match, but where the account NAME is different.
I am trying to find any records where the same account number is being used on more than one account.
All the fields are in a single table.
This is what I have so far, which just finds duplicate sort codes and account numbers;
SELECT CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE, CPL_ACCOUNT.BANK_NAME
FROM CPL_ACCOUNT
WHERE (((CPL_ACCOUNT.ACCOUNT_NUMBER) In (SELECT [ACCOUNT_NUMBER] FROM [CPL_ACCOUNT] As Tmp GROUP BY [ACCOUNT_NUMBER],[SORT_CODE] HAVING Count(*)>1 And [SORT_CODE] = [CPL_ACCOUNT].[SORT_CODE])))
ORDER BY CPL_ACCOUNT.ACCOUNT_NUMBER, CPL_ACCOUNT.SORT_CODE;
Thank you!
Hi, I have a cross-ref table (called MFC_CIBC_XREF) which links a bank account to a fund number and a general ledger number. It should be a unique relationship, wherein (the "=" means corresponds/links to)
Bank Account # "=" Fund #
For each bank account, there are sometimes multiple currencies being used, so each currency then links to a general ledger account, such that
Bank Account #.Currency = Fund #.General Ledger #
Sorry, if that's kinda cryptic, but here's an example:
Bank Account - ABCD123
Currencies operated in - CAD, USD
Fund # - F30
Based on the above, ABCD123 "=" F30, and including the general ledger numbers, ABCD123.CAD "=" F30.100, ABCD123.USD "=" F30.8121.
Basically, it's a way to keep track of not just the transactions for a particular bank account, but for the transactions in a particular currency.
Anyway, as I said, it's supposed to be a 1-to-1 relationship: each BankAccount.Currency should correspond to 1 and only 1 Fund.GeneralLedger. However, there are some entries in the table which have each BankAccount.Currency corresponding to multiple General Ledger numbers. Using my example above, ABCD123.CAD has two records in the table, one corresponding to F30.100, and another corresponding to F30.8101. This was probably because F30.8101 used to be linked to a different bank account, that got merged (ie, it might have been linked to ABCD124.CAD that then got merged into ABCD123.CAD).
Now I want to run a query on MFC_CIBC_XREF and find all the records where for each BankAccount.Currency, there is more than one Fund.GeneralLedger. I don't really know that much SQL, and even in Design View, I'm not sure of the statements to use. Any help is much appreciated.
Hello,
I asking to see if it is possible to run a query on a memo field. For an example I want to pull all records where the memo field contains the word "Test".
Is this possible?
Thanks!
Fen
Hi,
I hope you'll be able to help since I think it is going 2 B a tough one.
I'm looking for a code that will do the following:
I have a table (lets say customers) with a lot of names and instead of typing again and again the same name I would like to have a kind of an auto complete mechanism which will find the name from the table and use it to input it back to the table itself and if it isn't there then it will use the new name as an input, exp: in table customers I have the following names:
ACC, ABC, ABD, EFG .....
lets say I'm typing A then the ComboBox will show all the names that starts with A (e.g. ABC, ABD & ACC), if I will continue typing A=>B then only the names ABC & ABD will be shown then I can choose one of them or continue typing ABD which will cause only the name ABD to show up then I could use this as a name to input to the table or if I create a new name like ABDG then this one will be used.
I hope I didn't mess things up and explained myself correctly.
Thanks in advance
Sean
Is there a way to write a code or query to find the last value in the last Field (column) of a record in a table?
Or find the last field name that contains data, in a record?
I have:
1) a table (T) with fields (F1, F2, .... , Fk, .... Fn). I do not know "n"
2) a form (F) bounded to this table
I know that I have a control bounded to field Fk.
How I can find what control is ?
Something like this:
Dim MyCtl As Control
Dim ctl As Control
For Each ctl In Controls
If ctl IsBoundedToFieldFk Then
Set MyCtl = ctl
Exit For
End If
Next ctl
I have a field in a table called [PostalCode]. It is a Text field.
I have a report that used the [PostalCode] field. This is a report in a template that I downloaded from Microsoft.
If I enter "1234" in the field in the table, it looks fine in the table.
If I use the field in the report, it display as "1234-"
If I enter "1234567890" in the field in the table, it looks fine in the table.
If I use the field in the report, it display as "23456-7890"
I do not want any mask whatsoever and just want the value to be displayed as the user enters it.
I have looked at the properties of the field and there is nothing setup, I looked at the properties of the Form that edits the field and there is nothing also.
If I look in the actual report I see the following bit of code ,it is the control source for the Text box that is used to display a number of fields including [PostalCode]:
=DLookUp("[Suburb/City]","My Company Information") & ", " & DLookUp("[State]","My Company Information") & " " & Format(DLookUp("[PostalCode]","My Company Information"),"!&&&&&-&&&&")
I'd like to further automate our invoicing system and need a field which has the last day of the month an item was completed.
Currently we have a field in the table called [Date Done]. I'm planning on adding a further field [Tax_Point].
I'd like the field to select the [date done] value and enter the final day of that month, unless, the final date of that month is in the future. in which case it would need the current date.
We create invoices at sporadic times of the month, and in the next month for the previous month; hence the need for a system date check.
My update statement is shown below. What this is to do is change the PGTIN record in PP TBL to the GTIN that is in the UPC TBL where the UPC Code on the form is the same as the UPC Code in the UPC TBL. I am getting the error message "can't find field..." I have double and triple checked the field names but cannot find my error. Both PGTIN and UPC Code are text fields.
strSQL = "UPDATE [PP TBL] SET [PGTIN] = '" & [UPC TBL].[GTIN] & "' WHERE ([UPC TBL].[UPC Code] = '" & Me.UPC & "');"
What is wrong with this criteria...
RecSet.FindFirst ("[SomeField] = '" & mMESTO & "'") and ([CheckField] = True)
Memo field is called [Notes] and data is like this...
5/05/14 - Perry was on another call. LM 2/05/14 - Perry only at centre in the mornings, need to speak to him before sending samples. 13/06/13 - Perry in a meeting. lm 30/05/13 - See Little Hampton Early Learning - s/w Perry, has already received sample and info 29/05/13 - s/w Aspi, said to cb tomorrow and speak to Perry
I want to find each date in the Notes field so I can split the memo field data into another table where the memo field will become multiple records that hold date, text and customer/prospect ID fields. The customer table was easy because there was a <Div> tag before each date. However in the Prospects table there are no tags so how to change my vba code to search for each date before I split off the data.
Here is the part of the VBA code I used to find the <Div> tag in the customer notes field. How to find each date in the memo field? The date is in d/mm/yy format?
If Not rst.EOF Then
Do
StrSplit = Split(rst![Notes], "<Div>")
For x = 1 To UBound(StrSplit)