I finally got the result I wanted by simply putting the following in the Control Source of my unbound text box (CRNum) and deleting the VBA code.
Code:
=IIf([CurrentRecord]>Count([GrantLoanID]),0,[CurrentRecord])
JHite
Last Activity: Sep 23 '24, 11:46 PM
Joined: Nov 8 '06
Location:
-
I haven't marked any of your replies as the best answer but I really appreciate your help and learned some other good stuff from your replies.
I finally got the result I wanted by simply putting the following in the Control Source of my unbound text box (CRNum) and deleting the VBA code.
I used the IIf so that the current record for a new record would show... -
error msg reads:
"The Microsoft.Jet database engine could not find the object". Make sure the object exists and that you spell its name and the pathname correctly."
The name of the unbound textbox on frmGrantLoan is definitely CRNumLeave a comment:
-
How to assign Current Record value to textbox in form
I'm using MS Access 2003. I have a form named frmClient which has a subform named frmGrantLoan. [frmGrantLoan's underlying recordset is a query which pulls from tblClient and tblGrantLoan as linked by a ClientID.] On frmGrantLoan I have an unbound text box named CRNum. I have the VBA code (shown below) executed by the On Current property of frmGrantLoan. I'm trying to show the user the current record number by displaying it in the text box named... -
Thanks to all. I used Megalog's (corrected) code and it worked. Thanks for explaining about the EOF check. I didn't think of that in relationship to FindFirst.
In response to AJ's comment, I realize two different people can have the same first and last name. This is a small DB for use at my church and I will include instructions on how to delineate two people with the same first and last names (e.g., adding a middle initial to one...Leave a comment:
-
Ok. I tried your suggestion, but got the very same result as with my code. Case 3) still doesn't work correctly.
Are there any more suggestions?
Is there a better way to find duplication between records than using FindFirst?
Thanks again.
JaniceLeave a comment:
-
FindFirst with AND not working on single record
I'm using Access 2003 with MS XP.
Have table/records with FirstName (text field), LastName (text field), plus other fields. User enters FirstName, LastName and other info in form. Before updating table based on form input, I check to see if a record already exists for a person with that FirstName and LastName. If so, I give an error message and don't let the record be created. Pertinent part of form's BeforeUpdate code is as follows:... -
Sorry it's taken me so long to respond. I had to turn my attention to another issue and have just now come back to working on this Access database.
I now understand what you meant in the first place. When I set the subform's Enabled property to No in the design, it disabled the ability to access the subform but still showed the subform information for the first person (UserID = 1). So I used the subform's Visible property instead...Leave a comment:
-
I added lines 11, 23 and 28 based on your suggestion (see code below) but that did not change a thing. The subform is still "active" and shows info for the first UserID without the user having entered/selected anything in the combobox.
name of combobox is ChooseStaff
name of main form is frmStaffEntry
name of subform is frmStatusReport s
...Code:Option Compare Database
Leave a comment:
-
Subform showing records before choice made on Mainform
I’m using Access 2003 on Windows XP.
I have a Mainform (Staff Entry) which displays an unbound combo box named ChooseStaff. The Row Source for this combo box is a SELECT of the UserIDs and Staffer Names from a table (Staffers) which contains staffers’ names and other info.
When the user actually selects a Staffer Name from the dropdown combo box, the VBA routines for the combo box’s On Enter and After Update events... -
The subform name is "frmStatusRepor ts" and the routine is being called from the main form "frmStaffEn try" so I used the second set of code you provided.
It did just what I wanted!
Thanks again.
Janice...Leave a comment:
-
Thanks, Stewart.
I've added your suggested code at the end of ChooseStaff_Aft erUpdate() as follows:
...Code:Private Sub ChooseStaff_AfterUpdate() ' Find record for chosen staffer Dim rs As Recordset Set rs=Me.RecordsetClone rs.FindFirst "[StafferID] = " & Str(Nz(Me![ChooseStaff],0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark rs.Close Set rs = Nothing
Leave a comment:
-
Problem getting subform to refresh using default settings/option group
I am using Access 2003 on Windows XP.
This is a simple database that contains “tblStaffers” containing names of the office staffers, “tblProjects” containing names of the office projects, and “tblStatusRepor ts” containing the “StafferID” (link to Staffer table), the “ProjectID” (link to Projects table), a “Date” field, and a “Notes” field.
There is a main form, “frmStaffEntry, ” with a... -
Thanks for this last bit of information. It's always good to know someone else can independently duplicate the problem and your observation about the length makes sense. My report stretched over 3 - 11" x 8.5" pages (in landscape mode). I became so focused on the width issue that I didn't even consider the length.
Regards,
JaniceLeave a comment:
-
Thanks for the reply, Stewart. I did check the two properties you mentioned and made sure Force New page was set to None and that Keep Together was set to No. I'm still getting the blank first page. There is no sorting or grouping as this report is automatically generated from Tool --> Relationships, and simply shows the tables and relationships that the user elects to "show" in the configuration the user has laid out.
...Leave a comment:
-
Report generates blank first page
I'm using AC2003. Sequence of events:
a. Tool -> Relationships
I show my tables & relationships and arrange them to fit landscape mode.
b. File ->Print Relationships
This generates a report in portrait mode with name/date etc. in report header section, an empty page header section, and diagram of relationships in detail section.
c. I go into setup, change to landscape orientation... -
Thanks for the response. I have now creating a test report from scratch, first using the wizard then using just design view. The lines still show up on the printed report even though transparent is set. I am running Access 2003. Any other ideas?
Thanks,
JHite (Janice Hite)...Leave a comment:
-
Want to get rid of border on list when print report
I have constructed a report which show lists (from tables). When I print the report, each list has a box border with top and left sides a thin black line and the right and bottom sides a thin gray line.
In design view, I have set border style to transparent and border width to hairline.
When I look at the report on the screen, these lines are not visible, but when I print, there they are. How can I get rid of them?... -
Add new record via form where data entered is NotInList
I am using Access 2003. My Form has 4 fields:
MailingListID, auto generated – primary key
LastName
FirstName
NickName
I used the Combo box wizard to set up look-up box on the form. I answered the wizards question by saying “find a record in the form”, I choose the first 3 fields for the combo box and made field 1 width = 0. The wizard then created the unbound control box and generated the code shown...
No activity results to display
Leave a comment: