Cross Reference In Form
May 28, 2006
I have 3 fields in a form all have binding to the same table.
the problem i am facing is as follows
when i enter two fields in the form the third should calculate on its own.
Lets say the three fields are names field1, field2, and field3
scenario A
I enter field1 and field2. field3 should calculate on its own by adding field1 & field2
scenario B
I enter field2 and field3. field1 should calculate in its own by subtracting field2 from field3.
any time by changing any of the field1 or field3 the other one should change based on field2.
if field2 is changed then field3 should change.
i hope i have explained the problem in detail..
please help
thanks
aseem
View Replies
ADVERTISEMENT
Aug 13, 2012
I'm new to access and need to modify an existing database to get a report I want to create. I'm an Excel user and could accomplish what I want to do in seconds using Excel's Lookup functions, so I'm trying to do the same with Access Dlookup.
I have two tables in my database that tracks components in a hardware product. The first table is just a list of hardware units shipped. The second table is a list of various components (and versions thereof) used in each unit. The two tables are linked by a UNIT_ID field. I need to create a report showing each unit and what the various versions of the components are. My difficulty is that the component table is does not have a dedicated field for each component and each unit can be comprised of a different quantity of components. So the tables look something like this:
UNITS TABLE:
ID Customer
1 Joe
2 Bob
3 Bill
COMPONENTS TABLE:
ID Component Revision
1 Main Board A
1 Blue Board B
2 Main Board B
2 Blue Board B
3 Main Board A
3 Red Board C
I want to produce a report that looks like
ID Customer Main Board Blue Board Red Board
1 Joe A B --
2 Bob B B --
3 Bill A -- C
I setup queries to get revisions of each Board and am trying to compile into the full report, but can't figure it out. Right now I'm trying to use Dlookup to lookup data from one of the queries and insert into a larger table. I can lookup a single value fine, but can't figure out how to get Dlookup to give me the Blue Board & Red Board values for each of the system ID numbers in a master query (which would be a snap in Excel).
View 4 Replies
View Related
Nov 10, 2006
Hi - I've just followed a tutorial to create a simple parameter query based on the input received by 2 combo boxes on a form. This works ok, you select the 2 values on the form - the form passes it to the query which runs, the form closes and the query is displayed - all ok.
What I would like to do, is to display the results a bit more elegantly as the end users that will use this little database won't really like to see the query window. Is it possible to display the results on the bottom of the form? So that each time you click "Ok" on the form, it re-runs the query and displays the results. Any help would be great - but you'll have to explain it simply for me as I'm a newbee to all this! Thanks in advance:eek:
View 3 Replies
View Related
Jan 31, 2006
Hi,
I have created a form that is based on a cross tab query. I have a set number of fields in the form (1 day, 2 days, .....10 days).
The problem is sometimes when I run the query some fields disappear. for example for no records have an entry in the '2 days' field.
When this is out through the form then that whole column will read '#Name?' for all entries.
Is there some way I can clear the '#Name?' entry or replace with a zero on the form?
Hope anyone can help!!
Thanks!
View 1 Replies
View Related
Jul 16, 2013
I am creating a production type database with different stations (assembly 1, assembly 2, etc) with a master parts list that has every part. I want to cross reference each station input with the master parts list in order to make sure the part is in inventory. Can I create a button or to do this on the form? Or is there a better way? I would cross reference two fields.
View 1 Replies
View Related
Dec 4, 2005
hi
how do i reference a field on the parent form from the child form e.g.
i have a button on the child form which needs to read a value on the parent form and use it i na calculation etc
thanks
View 1 Replies
View Related
Aug 9, 2007
Hello,
I am using this thread in the criteria field in a query:
"*" & [Forms]![NameOfYourForm]![B1] & "*"
but when i run the query i am getting an error message stating:
This expression typed incorrectly, or is too complex to be evaluated.
any idea why i am getting this error in this given situation?
Thanks,
Eddie
View 1 Replies
View Related
Feb 17, 2006
I'm stumped and spinning my wheels like so many Milwaukee drivers after yesterday's snowfall. I have a series of forms named Survey1, Survey2, Survey3, etc. I also have a form for recording notes. When I close the notes form it is meant to post the notes into a hidden memo field on the survey form I am working with. I would like to keep the number of notes forms to one. I would like to refer to the survey form dynamically. Currently I am trying something like:
Dim SurveyForm As Form
Set SurveyForm = "frmSurvey" & Forms!frmMainScreen!SurveyDID
Forms!SurveyForm!MEMO1 = Forms!frmFreeText!Text0
I keep receiving an 'object required' error. Has anyone done anything like this? Can it be done? How?
View 4 Replies
View Related
Mar 25, 2006
Hi All
I am trying to reference a field on a sub form from a button on the main form. I want to read a field called "Date1" and I want Date1 from the first row, regardless of which row has been selected by the user. The statment used is
FrmGroupSubform.Form!TxtDate1.
Any ideas PLEASE
Steve
View 2 Replies
View Related
Aug 14, 2015
I have inherited maintenance of an Access DB that I did not design and I'm having trouble with their Navigation Form setup. I created a form with a list box and two date fields, and figured out how to build a filter for the form that would open a specified report on a button click. It worked great as a stand-alone form, but when I added it to a navigation control in a navigation form I can't seem to find the right path to tell the VBA where to look for the filter criteria.
In the screen shot, the report to be displayed is in the detail of the "Report Center" tab on the far right of the navigation form "Main." the form "FilterSelection" is inserted in the "NavigationControl0" section under the navigation tabs. Criteria is selected/entered in "FilterSelection" and needs to be passed to the filter of the report that is opened when the "Project Tasks" navigation button is pressed. As I mentioned, when I created the form I put a button on it to open the form with the filter (built via SQL in VBA) it worked beautifully. But now that it's on the "NavigationControl0" object Access errors and can't find the fields where the data has been selected/entered.
View 8 Replies
View Related
Apr 21, 2015
I had this line :
GBL_Tab05ID = Me.Tab05ID
and wanted to change this. I have this on the On Current event and it gives me an error because the data is not in the record source when the form opens. How can I change the me. to the formname? The form gets dynamically created so i have
Dim CurrentForm As String
CurrentForm = Me.Form.Name
then i put
GBL_Tab05ID = Forms(CurrentForm).Form.Tab05ID
but that did not work...what is the correct syntax?
View 5 Replies
View Related
Apr 4, 2014
Access 2010. I have a query that I referenced it to a combo box in a form.
If I rename the combo box, save it, and then rename it back to the same name it works fine for a while and then stops working again.
View 5 Replies
View Related
May 16, 2013
I have a DLOOKUP which is used to auto populate a field on my Income Commitment Form based on a field on my Expenditure Commitment Form.It works perfectly when you enter a new commitment.Currently you enter a new Expenditure Commitment, save it but don't close it, then from the Expenditure Commitment Form there is is a button to enter a related Income Commitment. You save the Income commitment then close it, then close the expenditure form and you are on the Client screen with both these forms now showing as subforms, both have record selectors.
My issue is, that sometimes the Expenditure Subjective can change, so from the client form you use the record selector to open the Expenditure Commitment and select the correct Subjective, you then save it and close it. You should then click on the Income Record selector which opens the income commitment, but when you re-click on the Combo42 button, it doesn't reselect the Income subjective and I think it is because the Expenditure Commitment which is what it is looking up isn't open. My issue is how do I get the DLOOKUP to work and refer to the Commitment Form if it is a new commitment, or the Subform if I have gone back in through the record selector.Here is the code I have.
Private Sub Combo42_AfterUpdate()
If (Me.Combo42 = 1) Then
Me.IncomeSubjective = DLookup("[Subjective]", "tblIncomeSubjective", "[ExpSubjective]='" & Forms![frmCommitments]![cmbSubjective] & "'")
Me.OtherIncomeConfirmed.Enabled = False
Me.OtherConfirmedBy.Enabled = False
[code]....
View 1 Replies
View Related
May 8, 2014
I have form named SearchForm and there is unbound subform named Search_blank. Depending on button in menu is used source object of Search_blank. One of the source objects is form named MeasureForm and it has subforms named MeasureForm_sf1 and MeasureForm_sf2. How can I reference public sub in MeasureForm_sf1 from SearchForm? How can I reference public sub in MeasureForm_sf1 form MeasureForm_sf2?
There might be a problem with unbound form. I tried reference public sub named GetLastVal from SearchForm that is in MeasureForm:
Code : Me!Search_blank.Form.GetLastVal
and it was working. Than I tried reference public sub named GetLastVal2 from SearchForm that is in MeasureForm_sf1:
Code : Me!Search_blank.Form!MeasureForm_sf1.Form.GetLastVal2
and I got RunTime error 2465, cannot find MeasureForm_sf1
View 4 Replies
View Related
May 24, 2006
I am working on an form (using Access 2000 and VBA) that stores registration information, along with events that were attending, in a table and later retrieves that same information from the table to display the regist. info, events attended, and allows the user to change any current info along with adding new/recently attended events to each record.
I am currently allowing a max of six events, along with event details, to be stored and I am running through six different if statements to match the Event_List (name of the combo box that contains "Event 1 Event 2...Event 6") to the appropreiate information in the table. I have started to simplify the code to remove the redundancy by running a Do While to to find the correct Event by using an integer variable that starts at 1 and is incremented until it matches the 7th character (the number portion) of the Event chosen from the Event_List. After this I want to use that integer variable to say what event information in the table to gather. My problem is this: I have tried many different varieties of the following code and I am unable to make it work. I had never used Access up until a week and a half ago, and a few days ago I went and bought the book "Access VBA Programming for Dummies" but it doesn't help for this problem. Here's part of the code:
Variables (Event_Name, PMT, Receipt, Payment_Type, PIF) refer to Form textboxes while the variables in the [] refer to table field names.
'------Code I'm Currently Working On-------
Dim intX As Integer
Dim EventVal As Integer
Dim strX As String
intX = 1
EventVal = Val(Mid(Event_List, 7, 1))
Do While EventVal <> intX And intX < 6
intX = intX + 1
Loop
strX = str(intX)
Dim E_Name As String
Event_Name = [Event strX ]
PMT = [PMT strX]
Receipt = [Receipt strX]
Payment_Type = [Payment Type strX]
PIF = [Paid In Full strX]
'-----Old Code That I'm trying to simplify-------
If Event_List = "Event 1" Then
Event_Name = [Event 1]
PMT = [PMT 1]
Receipt = [Receipt 1]
Payment_Type = [Payment Type 1]
PIF = [Paid In Full 1]
Camp_Site = [Camp Site 1]
Cabin_Number = [Cabin Number 1]
ElseIf Event_List = "Event 2" Then
Event_Name = [Event 2]
PMT = [PMT 2]
Receipt = [Receipt 2]
Payment_Type = [Payment Type 2]
PIF = [Paid In Full 2]
Camp_Site = [Camp Site 2]
Cabin_Number = [Cabin Number 2]
ElseIf Event_List = "Event 3" Then
Event_Name = [Event 3]
PMT = [PMT 3]
Receipt = [Receipt 3]
Payment_Type = [Payment Type 3]
PIF = [Paid In Full 3]
Camp_Site = [Camp Site 3]
Cabin_Number = [Cabin Number 3]
PLEASE HELP!!!!
View 3 Replies
View Related
Dec 5, 2014
I am trying to see if it is possible to store colors in a form or table and then reference them while in VBA. What I am hoping to do is when I write all my code for command buttons to change On Got Focus, instead of writing xxx.backcolor = RGB (255,255,255) i could do something like xxx.backcolor = Forms!HiddenColors!Command That way if i want to ever change the color scheme of the db, I can change it in one place rather than hunt lines of code.
I have played around but with no success. Technewonline is a website that specializes in introducing the latest technologies such as Best Tablet Android Have Price Under $200 and Best tablet of Apple in 2014 and The Best Midrange Smart Phone In 2014 and Top Best Ultrabook Of 2014 and The Best Phones 4G Valued At Under 300 USD is also a website for sharing your tips about computers, mobile phones and tablets, products are available from leading supermarkets will surely satisfy you.
View 1 Replies
View Related
Jan 23, 2014
For further details see here: [URL] .....
I have a form with two subforms (the second is the child of the first). I want to filter a combo box in the second subform using the record of the first as the filter.
When I open the form from the object list I get this '2455' error. When I press "end" and the form opens the requery does what I want it to do with no further error, using this code in the "got focus" event of the combo box in the first subform:
Code:
[Forms]![form_candidates]![Form_skill_subform].Form![skill_ID].Requery
I have included this before the requery:
Code:
On Error Resume Next
I have seen the use of
Code:
.recordsource = "..."
instead of the requery
View 2 Replies
View Related
Mar 12, 2013
Ok, this is what I have:
I have setup my main form with 2 subforms to mimic a split form; this works fine.
To search for a record, I am using a cbo on the main form which I would like the user to select from. Once selected, then this will populate the first subform.
This is the code I have for the cbo:
Dim intAnswer As Integer
If IsNull(Me!cboCaseNoCFDWit) Then Exit Sub
With Me!sfFocus.Form.RecordsetClone
.FindFirst "Me!sfFocus.Form!CaseNumber = """ & Me!cboCaseNoCFDWit & """"
If Not .NoMatch Then
If Me.Dirty Then Me.Dirty = False
[Code] ....
I get an error at the .findfirst; states that the Microsoft Engine does not recogize Me!sfFocus.Form!CaseNumber
And the reason I am not using a regular split form is because I cannot, get the form to the size that I want. The splitform works great with the code above (a few changes to it of course), but the bottom of the splitform (datasheet) is too long and I cannot shorten it.
View 2 Replies
View Related
Apr 2, 2014
I have a Main form 'frmEmployeeInjury' with a subform 'frmInjuryDetails'.On the subform is a date field 'dteDateofInjury' and on the main form a command button 'SaveRecord'
What I am trying to do is hide the command button on the main form until a date is entered in the 'dteDateofInjury' field.I have looked online and found information on main form and subform referencing, but I can't follow it very well without examples.
Code:
Private Sub dteDateofInjury_AfterUpdate()
Me.Forms![frmEmployeeInjury]![SaveRecord].Enabled
'Me.Parent.cmdSaveNewInjury.Visable
'Me.Parent.cmdSaveNewInjury.Enabled
End Sub
But I get 'method or data member not found'.
View 3 Replies
View Related
Sep 9, 2013
I have a form with 10 combo-boxes. Once user selects a value in CB1- CB2 becomes visible and active. I am trying to run a dynamic query- where the selection of the combo-box is used to select a column from my table called "dbo_animals"
To elaborate: CB1 contains the following values
Code : elephant, giraffe, bufffalo, tiger, lion
Once the user selects elephant in CB1, CB2 becomes active and I select tiger next. So the query becomes like. The process can go on and the user can select up to 10 animals
Code : **SELECT elephant, tiger FROM dbo_animals**
Problem: I am able to create the query with string manipulation- Unfortunately due to the way the loop through controls is set up- The query is unable to convert the text into a reference. If I hard code it as
tempquery = "SELECT [Form]![Animal Finder]![CB1] FROM dbo_animals" MsgBox(tempquery)
It looks like
Code : SELECT Elephant from dbo_animals
This is how I want. But, since i am looping through CB controls, I have it set up as
Code : tempquery = "SELECT" & " [Form]![Animal Finder]![CB" & i & "] FROM dbo_animals"
and this shows up as
Code : SELECT [Form]![Animal Finder]![CB1] FROM dbo_animals
Thereby giving me an error saying that the reference is not valid and asking me for a parameter value.
This makes sense, since it is unable to evaluate the text as a reference.
How do I correct the text into a reference? Or How do I build a query with adjusting columns based off selections from a combo-box and loop through the comboboxes in the form while auto-updating the query?
View 4 Replies
View Related
Mar 25, 2014
In my form (source tblJobs) i input a contract number from tblContracts. but i would also like it to show the contract address when the contract number is entered. Both fields belong to tblContracts but i only wish to enter the contract number, the address will just be used as a reference as it isnt something i would have to enter again.
So for example
112 - Main Road
If i enter 122 in my ContractNo field i would like it to show the contract address for that specific contract.
Can i do this? If So how?
View 5 Replies
View Related
Feb 20, 2014
I changed the name of a form referenced in the Autoexec macro from "frmLogin" to "frmLoginPW". However when I Open Access 2010 instead of Form "frmLoginPw" opening I get a message box saying that the system can't find "frmLogin". If I do a search through all the code in the project there is NO reference to "frmLogin" as I changed any code to refer to "frmLoginPW" and recompiled the project.
I can not get rid of this error and if I press the OK button on this error message ( from MS Access not my error coding) the "frmLoginPw" opens and the code in the form works. (Password authentication etc etc.). I deleted the AutoExec macro and remade the macro and referred it to the new form "frmLoginPW", this did not work. I loaded the Accdb file using the Run command and the /Decompile command line parameter to no effect.
View 3 Replies
View Related
Aug 3, 2011
I need to reference a form called "Search Pro" that is within my navigation form. I have tried the normal ways of referencing subforms and it does not seem to be working.
Do you have to reference forms differently if they are within the navigation form??? For some reason all of the forms within the Navigation form are named "NavigationSubform" and whenever I try to change one they all change. So then what do I need to reference? Do i need to reference the tab index???
View 3 Replies
View Related
May 11, 2007
I am trying to make cross tab ..to merge the two value of the same column.A B AIRA B GROUNDA C SEAi am trying to make..A B AIR & GROUNDA C SEA..any idea?........let me know, i really need this query to solve the problem..thanks guys..
View 2 Replies
View Related
Aug 2, 2012
I'm trying to open a recordset in vba and I'm getting the 3061 runtime error,Expected 1.
I'm trying to open a recordset with a query that uses a reference to a form control.
Code:
searchtable1 = "qInVisio_RSV"
Set rs = db.OpenRecordset(searchtable1, dbOpenDynaset, dbSeeChanges)
This is the sql of the query:
The highlighted parted is the form referance ( I know it's obvious, just for easier spotting )
Code:
SELECT dbo_FOLIO.FOLIOID, dbo_FOLIO.KIND, dbo_FOLIO.RSVID, dbo_FOLIO.CHKIDATE, dbo_RLIST.ROOMID, dbo_ROOM.ROOMNO, dbo_AGN.LINAPRG
FROM (dbo_ROOM INNER JOIN (dbo_FOLIO INNER JOIN dbo_RLIST ON dbo_FOLIO.RSVID = dbo_RLIST.RSVID) ON dbo_ROOM.ROOMID = dbo_RLIST.ROOMID) LEFT JOIN dbo_AGN ON dbo_FOLIO.AGNID = dbo_AGN.AGNID
WHERE (((dbo_FOLIO.KIND)=101) AND ((dbo_FOLIO.CHKIDATE)>=[Forms]![frmCleaningPlan]![DTPicker]));
also as you can see it's a datepicker control, so the value is a date...
View 2 Replies
View Related
Mar 31, 2006
I want to obtain the value of a third variable using the first one as row heading and the second as column heading. I can't get this using a cross table query.
Is there another way of doing it???
Thank you
View 2 Replies
View Related