Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS ACCESS






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.








Sponsored Links:

Related Messages:
Dlookup To Replace A One Field Subform
Thought this would be a good solution, but having trouble with the syntax.

=DLookUp("[StartDate]","[tblJobHistory]","Me.[EmployeeID] =" & tblJobHistory!EmployeeID & " AND Min([tblJobHistory]![StartDate])")

I am getting the dreaded #Name? can I use the Min command like this?

Any help appreciated:)

View Replies !   View Related
Newbie Combo Box - Form/subform Dlookup ?
Here goes my problem, and how I am attempting to solve it, but I am open to advice.

I have a table that contains all of the bird species in the US; (fields: common name, scientific name, species ID) I want to be able to update another table, (birds to chase) with particular bird species that I have not yet seen, (fields: species ID, common name, scientific name) and link that to a sub form that links to another table (chase birds locations) which shows geographic location data as to where I might be able to see new/unseen birds; (fields: species ID, date, other geographic data).

My problem is this. I created a form for the unseen birds table that allows me to only select a bird using a combo box of birds from the larger table of US birds. I want to select a bird using the common name in this box, and have it automatically populate the scientific name and the species ID into the birds to chase table. I link this tables to the location table using species ID.

A workaround that I have used is to close the form, run two update queries that populate the proper fields for scientific name and species ID and to re-open the forms. Although this works, when I return to the form it does not return me to the proper record, so that I can enter data into the sub-form.

Obviously this is a workaround and will work for me in the short run, but I want to learn how to do it properly within Access 2003.

I'm not a programmer and hence do not have access to the .NET tools or Visual Basic. Any hints for me out there?

Patrick

Victor, Montana

View Replies !   View Related
Subform Blues - Data Entry Setting Itself To 'No' & Requerying 1 Subform From Another
2 Subform problems

I have a data entry subform that is only supposed to show an empty record ready to be populated, and a display records subform that is supposed to show all the records. The subforms are both on the same tab of a tab control on my main form.

Problem 1:
The data entry subform shows all the records rather than a blank record. Something on my main form is causing it to show the records when it should not. Any ideas? The Data Entry is set to Yes.

To try to isolate the problem, I created a new form and added the subform to it where it behaves properly:confused:

I then added Me.DataEntry = True to the form open to see if that would solve my problem but it still sets the data entry to no.

If I have the properties box open when in form view of my main form, I can set the data entry to Yes and it works fine until I move to the next record of the main form when it resets to no. Teraing my hair out here.:mad:

My final attempt was to search the entire project to see if there is a "DataEntry = False" somewhere but there isn't. What is setting this property? Any ideas where I should look?


Problem 2:

After entering data in the first subform (data entry form), I want to re-query the second subform but I just can't get the syntax right. I have wrestled with the "Syntax for subs" document downloaded from http://www.mvps.org/access/forms/frm0031.htm (Microsoft MVP site) but to no avail.

My main form is called fdlgPrjDetails, the data entry is via fsubPrjCommentsUsersDataEntry and the subform I wish to requery is fsubPrjCommentsUsers.

None of the attempts below worked giving a cannot find control error.


Private Sub Form_AfterUpdate()
On Error GoTo ErrHandler

Me.Requery

'Me!fsubPrjCommentsUsers.Requery
'Me!fsubPrjCommentsUsers.Form.RecordSource.Requery
'DoCmd.Requery ([fsubPrjCommentsUsers])
'DoCmd.Requery [fsubPrjCommentsUsers]

ExitHere:
Exit Sub

ErrHandler:
MsgBox Err.Number & " - " & Err.Description & Chr(13) _
& Chr(13) & "Error in fsubPrjCommentsUsersDataEntry: Err 003"
Resume ExitHere
End Sub


Any Ideas?

Both problems have me stumped so I'll be grateful to anybody with a scoobie on this.:)

View Replies !   View Related
Unbound Subform Counts Toward Two Subform Depth Limit?
I didn't know that Access is supposed to only have 2 subform deep. On my form, I have three subform deep. However, the master form is unbound, not linked to subforms, and nothing more than a container for tab pages which contains their own subforms and has no code directly associated with data. There is a code to move subform to new record when tabs changes, but that's all.

Does that mean there is really only two subforms deep from the topmost form on the tab page or the subform depth has been exceeded whether the master form has no role in data transaction?

View Replies !   View Related
Filtering Field In 2nd Subform Based On Selection In 1st Subform
I almost have this form done.

I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.

What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.

I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.

I hope that someone can help with this!

:confused:

View Replies !   View Related
Filtering Field In 2nd Subform Based On Selection In 1st Subform
I have a form that is almost complete

I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.

What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.

I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.

I hope that someone can help with this!



View Replies !   View Related
Limiting A Dropdown List In A Subform From Within The Subform
I have a subform within a main from which switches by entry. The subform is a datasheet. I have two dropdowns in the datasheet and I want to limit one off of the other. I keep getting a prompt. The prompt is Forms!CLTS Subform!Environment. To my understanding it looks like it can't find Environment. Does anyone know the proper format.

This is my code for the second drop down in the subform.
Also special note Environment is what I am referencing to and it also is in the subform.

SELECT PackageName.PackageNameID, PackageName.PackageName, PackageName.EnvironmentID
FROM PackageName
WHERE (((PackageName.EnvironmentID)=[Forms]![CLTS Subform]![Environment]))
ORDER BY PackageName.PackageName;

View Replies !   View Related
Open Subform At Certain Recordset Based On Subform
I have a main form which has a subform control in it. when the main form is opened the sub form control contains a list of events in a sub form (a summary view), for the person selected in the main form.

I want to change the subform to a different one which shows the whole event when i click in a field on the subform, based on the event i clicked in. ( i can do it by opening a new form but not with a new subform)

I can make the form change using the following code:

Private Sub Notes_DblClick(Cancel As Integer)

Me.Parent.subformhere.SourceObject = "eventsfullrecord"

End Sub

What code do i need to make it open to the record i clicked in the summary form, rather than just opening to the first record every time?

Not very good at passing recordsets

Thank you

View Replies !   View Related
DLookup Not Right
Can anyone see where I've gone wrong. The purpose should be if the value in the query is 0 or less (a minus amount) and the order number and part number matches the order number and part number on the form then a warning should appear. It's not picking up the record for flagging when it should:

If (DLookup("OutstandingQty", "qryremaininginvoiceamount", "OutstandingQty <= 0" & " And [OrdNo] = " _
& Me.[OrdNo] & " And [InvPtID] = " & Me.InvPtID)) Then

View Replies !   View Related
Dlookup's
Hi,

I am pretty new to Ms Access.

I have a table which has a list of trades (Builder, Carpenter, Painter etc) going down the side and going across the top I have the number of employees (1Emp, 2Emp, 3Emp). The values in the table show the charge for that number of employees for that trade.

I have a form which has three combobox's that show the Number of Manual Employees, Number of Clerical Employees and the number of working directors and a combo box that shows the trade. What I want to happen is when the user clicks a command button on the form the charge for the total number of employees for that trade is shown in a txt box.

I have the following code attached to my button:

Private Sub GetValue_Click ()
Dim TotalEmployees As Integer

TotalEmployees = [NoManual] + [NoClerical] + [NoWorkingDir]

Value = Dlookup(TotalEmployees & "Emp", "ValueTable", "[Business] = Forms![Form1]![Business]")

End Sub.
When I type "[3Emp]" in the Dlookup it will work fine and finds the charge for that trade. But when I pass it the Result from the addition and concatenate it with "Emp" it doesn't seem to work.

I originally thought it was because I declared the TotalEmployees as an Integer by I also tried declaring it as a string - to no avail.
I just can not figure out why it will not look up the TotalEmployees.

I would much appreciate any help on this on any suggest as to how I can get the values from my table.

Charlene

View Replies !   View Related
How Do I Use DLookup???
I am using Dlookup to look up LastName, I have

AssignBy = DLookup("[LastName]", Employees, "POC =" & Me.Combo22)

I am getting an ERROR ""You entered an invalid argument in a domain aggregate function"

I need AssignBy = LastName from Employees Table where POC=what is selected in Form for Combo22.

ex. POC=3 go to Employees Table find POC that equals 3 and get the LastName.

Please Help!!

Jessie

View Replies !   View Related
DLookup
Please help prevent my laptop from being smashed against a wall numerous times and then being thrown out the window, after all, the wall is mostly innocent in this situation.


I have read post upon post and other dim references to DLookup in the Access Help file, etc. I am generally a bright guy (although inexperienced in DataBases, VBA and some forms of lovemaking) but I have not been able to figure out the DLookup function. Could you please give me a VERY simple explination of how to use this function and it's expressions?

If it will help, we can use the following senario...

Table_Special_Needs
Special_Need_ID
Special_Need (Data Includes: Initiatives, High Ropes, etc.)
Standard_Price

Table_Event
Event_ID
Initiatives
High_Ropes
Extra_Linens

I would like to be able to add a price field for each item in the Event table and have the table lookup the price from the Special Needs table.

Many thanks for any help you can give.

t
:confused:

View Replies !   View Related
Dlookup ?
I would like to know if i am doing an Inventory in an Invoice Program and i want the [quantity] from a subform on the Orders Form to be deleted automatically from a Products Table , Would the Dlookup Function Help and if yes how would i implement it ?

Regards Dups

View Replies !   View Related
Dlookup
Dear all,

Firstly, thank you all for allowing me into your group!

Secondly however, PLEASE HELP!

I've created a database for storing students details and exam records for my university department, and everthing is fine except for one final problem: In order to allow final grades to be calculated all student marks have to be stored numerically, but each number corresponds to a code: eg N=0, G2=1, G1=2 .... A1=22

I'm trying to get the student marks report to display not just the numeric code, but also the final code eg, C2. To do so I've been trying to use DLookup as follows:

Expr1: =DLookUp("[Grade Code]","Lookup Table","[Score]= Forms![Total Grade]")

where "Grade Code" is a field in the Lookup Table (ie, it lists the A1, A2), "Lookup Table" is a table listing all the numbers and their corresponding codes, "Score" is the numeric score in the "Lookup Table" table, and "Total Grade" is a calculated (Numeric) field in the open report (though created in an accompanying query). I just can't get it to work however!! When I run the report, the column is just blank, and nothing I do seems to be able to get the corresponding code (Eg, B1, B2) to appear on the report!

Please help me if you can, I'm not kidding when I say my job is on the line with getting this database to work! Once I've got this sorted, all I need to do is find a way of letting the secretaries input the alphanumeric code, but the database automatically stores that as a number. I figured however, that sorting the first problem will go some way to finding out how to sort the 2nd one!

Thank you all just for being out there!

View Replies !   View Related
Dlookup
Do the Access functions dlookup, etc. cause performance issues when using remote SQL servers where the data is held?

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved