I have a database in Access 2000 that contains multiple entries for each individual - in this case they are 'visits'. I wish to create a query that shows the first and last visit only on any particular day for each individual.
Can anyone help me?
I have one query that the main form is based on and another query the subform is based on.
I linked the subform to the main form by a common field "Branch"
My main form displays Grouped Employee overhead totals based on Branch
Example:
Branch 1 Employee overhead cost...........15,000.00
Branch 2 Employee overhead cost...........25,000.00
The subform displays grouped branch expense overhead totals based on Branch
Example:
Branch 1 Branch expense...........125,000.00
Branch 2 Branch expense...........155,000.00
I am trying to display both the employee overhead total and branch expense in the Branch Header of the mainform.
Example:
Branch 1 Employee Overhead...15,000.00 Branch Expense...125,000.00
Branch 2 Employee Overhead...25,000.00 Branch Expense...155,000.00
I tried using an IIF statement in a text box in the mainform branch header section to return the branch expense if subform branch = mainform branch.......When I run the report I get all of the Employee overhead but only the last record for Branch 2 branch expense displays.
Example:
Branch 1 Employee Overhead..15,000.00
Branch 2 Employee Overhead..25,000.00 Branch Expense...155,000.00
Then, I tried just putting a text box in the main branch header subreportName.Report.TotalField
When I run the report it returns all employee overhead with the Branch 2 total for every branch in the mainform...
Example:
Branch 1 Employee Overhead..15,000.00 Branch Expense...155,000.00
Branch 2 Employee Overhead..25,000.00 Branch Expense...155,000.00
I have several option groups with options Yes/No (radio buttons) on a form. When the form is opened they should all have a default of No. This was working. Recently I added another Yes/no option group to the form. However, this option group shows no default when the form is opened. This option group is programmed the same as the others. The frame has no default value. The option value of "No" is 0 and of "Yes" is 1. The underlying fields of all option groups are of Number type with a default value of 0. How can I get the new option group to default to No?
Thanks for your help.
I have an Option Group I created (simple Yes-No) in a form that links to a Yes-No field in a table. I have the default set for this field to "Yes" in the form. The option button works when the form is first opened. If I cange the option to "No", that works. Here's the tricky part that is giving me eye twitches...
If I change the option back to "Yes", the radial button doesn't reflect the change...but it does change the table data! Why do the radial buttons stop changing? Even when I close the form and open it again, the data in the table is no longer reflected with the buttons in the form.
I have 64-bit, Access 2010.
I am new to access and I recently encountered a double click issue
My form loads perfectly on double click event but it shows the first record instead of selected record.
My search is based on a PersonID but each PersonID has different WorkID that I wish to display on double click but it always shows the first WorkID instead of my selected record
I have tried changing the filters in the form properties but it still doesn't work for me.
Here's my code:
Private Sub SearchResults_DblClick(Cancel As Integer)
DoCmd.OpenForm "WorkForm", , , "[PersonID]=" & Me.[Searchresults], , acNormal
End Sub
[Searchresults] draws information from my Query
Query information:
PersonID... WorkID... Type......Location
1234..........1............Paint .....Address A
1234..........2............Electric...Address B
1234..........3............Floor..... Address C
hi all,
i have question about showing last record in a form. i have 2 table, ra and repair tables. each ra_id is consists of many repair. each repair have time and date stamped. I want to show in tabular view all of the ra records including the last update of each repair per ra_id in a form. When I select some fields in ra + time in repair table, the form showing only records that has repairs ONLY, but not all records. And also display time BUT not the last update, instead showing ALL repairs. can any one help, if i want to show all records plus time but ONLY the last update on the repair. I don't know much vba, so please tell me what to do in macro or expression builder????
Hi,
I have a main form that displays records with a couple of sub forms and it also has a button to open another form which is based on the same table main table but different sub forms.
All I am trying to do is open the second main form by clicking the command button and to automatically display the same main record as shown on the first form before clicking the button.
Sounds simple but I can't find how to do it.
Any ideas appreciated........Stuart
I've got a text box, populated by a query.
(it looks like a list because it's tabular view)
you can always see the empty record... any way you can stop it showing?
or failing that, I have a dblclick event on all the textboxes. But It doesn't work with the blank record..
this is quite frustrating.. any help please?
After setting the source of a from to a table, a form shows a blank record and allows you to input one row of record at a time.But is it possible to have the form show a specific number of blank records, say 6, on each page of the form?I want the user to enter 6 new different records on just 1 page of the form without using the navigation buttons to move between records.
I need to design a database system for students to choose their modules.Each student needs to choose exactly 6 modules and I want the student to input all their 6 choices on 1 page of form, instead of using the navigation buttons to switch to a blank record to input each module.
I have make relational table on query, but some record not sohowing on query, although i have the record on table.
for example, i can't see the record with code :
131401.007
541000.00*
is the anything wrong?
thanks
Have a strange problem. I've created a form with multiple subforms and if there is a record for the subform to display everything looks fine. However, if there is no record for the subform it does not show anything on the screen or if printed.
I would like for the fields on the subforms to show no matter what but I can't figure out why it doing this. I've checked the display parameters and everything is set right.
How do I make the forms show no matter if there is data to display?
Any help is greatly appreciated -
Thanks
Hello,
I have another question about ACCESS forms. With VBA, I want to change the record showing when I open a form.
I see there is a member "CurrentRecord" so I tried in the class module
Me.CurrentRecord = 2
I compiles but gives runtime error that is write-protected and cannot be changed. In the ACCESS help I read you could do it but HOW?
What should I do ( I am using ACCESS 97 ) ?!
Thanks in advance
Victor
PS : Is it true that VBA in newer versions of ACCESS has more features/functions etc?
Hi all,
Is it possible to open a form so that it shows the new record fields only? I want to create a form that only allows the user to enter data and not view any of the records in the table the form is linked to.
Cheers,
Matt
Hello,
I've searched and searched but I cannot find a solution to my problem.
I have a combobox with a list of manufacturers. When you select one, the URL of their website shows up in a text box below.
I have a 'remove' button on the form and when you click it, it removes the manufacturer from the database. This works fine, but '#deleted' gets displayed in the text box. I've tried Me.Refresh, Me.Recalc, and requerying both the form and the field (I'm using Me.requery on the combo box, and it works fine there). I've also tried assigning the text box a blank value, but this doesn't work either.
I have the control source of the text box set to the URL field of the table that serves as the source of the form.
Here is my code:
Dim ManufacturerList As Control
Set ManufacturerList = Forms!frm_EditManufacturerList!cbo_Manufacturers
Dim URLBox As Control
Set URLBox = Forms!frm_EditManufacturerList!URL
Dim networkequipmentdb As DAO.Database
Dim RemoveManufacturer As DAO.Recordset
Set networkequipmentdb = CurrentDb
Set RemoveManufacturer = networkequipmentdb.OpenRecordset("ManufacturerSites")
RemoveManufacturer.Edit
RemoveManufacturer("Manufacturer").Value = ManufacturerList
RemoveManufacturer("DownloadPage").Value = URLBox
RemoveManufacturer.Delete
Me.cbo_Manufacturers = Me.cbo_Manufacturers.ItemData(0)
Me.cbo_Manufacturers.Requery
Me.URL.Requery
Me.Recalc
Me.cbo_Manufacturers.Value = ""
Me.URL = ""
Any help would be appreciated.
Thanks
My data is structuredin the following format:
ITEM......................PRICE
X..............................$10
X..............................$10
Y------------------------$25
Y------------------------$25
I would like to calculate each item's price only once in the Access report footer. However, when the SUM formula is entered I get the cumulative price for each item (i.e. $70 as opposed to $35).
Is there any way I could just show the price of the first item of the group only? I'm thinking of creating a new field in a query and adding 1s and 0s, but don't know exactly how.
Hi,
I am currently working on a database and I'm using a form to view the records. A standard Access behavior is to show an empty record (new record) as the last one. is there a way to disable this "feature". I would like my form to display only the records, which are really filled with data and not show the blank one at the end??
Thanx in advance
Luke
I have a database which has one main form linked to a table which has 325 records in it. The problem is when you open the form it says there are only 324 records :confused:
I have gone through the table comparing the records with the form records, I found what I thought was the 'missing' record. But when I did a find on the form I managed to retrieve the record.
Can anyone help as I'm a bit baffled.
Thanks
S
Hi all,
How to open a fresh form without showing the existing records in the DB.
I.E I've 5 records in the DB. When I click on the add new form command button
Code: DoCmd.GoToRecord , , acNewRec
I want to have a fresh piece of form + I dont want the records which is in the DB to be shown.
Is that possible.
Thank You.
How to hide a Record when "False" or a box is unticked on the report.
I've used a Query for the report and figured it would go in the Criteria for the tick box but can't find the code I need (I am sure it is simple)
i want to get the id of a new record in a msgbox.this is my code
Code:
Dim MSG As Integer
Dim ExistentID As Long
ExistentID = Nz(DLookup("P_ID", "tbl_Personal_Information", "Full_Name = Forms!frm_New_Person!F_N"), 0)
If ExistentID > 0 Then
[code]....
my problem is with MSG2 when i click save it will save the info but it will not show the message MSG2.if i remove this {" & [P_ID] & "} from the MSG2 it will open.if it's existed she don't open and if i press again it will show the MSG1
I have a form with command buttons to preview reports. For some reason only the first record shows up on the reports and not the current record.
View 10 Replies View RelatedFYI: I'm new to Access, but have some knowledge of SQL and VBA. I'm using Access 2000.
I've looked through past posts to no avail.
I have a form which allows users to supply one or more criteria to subset a
recordset. The "Execute" button on the form kicks off VBA which builds and
runs a SQL statement using the selections made in the form.
The recordset that the SQL runs against can have multiple rows for each
ProjectID -- based on a combination of a couple of fields. For instance, the
following is possible:
ProjectID---StartDate---Employee---ProjectCategory---... <other fields>
1-----------01/01/05---Herman-----App Dvlpmt
1-----------01/01/05---Hortence---Consulting
1-----------01/01/05---Herman-----Consulting
1-----------01/01/05---Hortence----App Dvlpmt
If the user wants to select ProjectIDs where Employee="Herman" -- without
making a selection on ProjectCategory -- I want only one of the two
"Herman" rows above to be returned. And I don't really care which one.
(Similarly if the selection is only on ProjectCategory)
The problem is that, using the code below on the example above, both
"Herman" rows are returned. I've tried numerous approaches -- this being
the most recent. The SQL statement is being built as I expect, and it's executing.
It's just not giving the results I want/expect. Also, I've hardcoded selections
into a stored query similar to the one below, and it works. Any ideas on what am I doing wrong?
Private Sub cmdExecuteQuery_Click()
Dim strSQL As String
strSQL = "SELECT ProjectID, first(ProjectName), " & _
"first(StartDate), first(EndDate), first(ProjectActive), " & _
"first(Sector1), first(Sector2), first(Sector3), first(ClientShortName), " & _
"first(Employee), first(ProjectCategory) " & _
"FROM qryProjectsForReport WHERE (ProjectActive = "
Select Case optStatus
Case 1
strSQL = strSQL & "True) "
Case 2
strSQL = strSQL & "False) "
Case 3
strSQL = strSQL & "True or ProjectActive = false) "
End Select
If Len(cmbCategory) Then strSQL = strSQL & " AND ProjectCategory =
" & cmbCategory
If Len(cmbMember) Then strSQL = strSQL & " AND Employee = " & cmbEmp
If Len(dtStartDate) Then strSQL = strSQL & " AND StartDate >= #"
& dtStartDate & "#"
If Len(dtEndDate) Then strSQL = strSQL & " AND EndDate <= #" &
dtEndDate & "#"
If Len(cmbClient) Then strSQL = strSQL & " AND ClientID= " & cmbClient
If Len(cmbSector) Then
Select Case cmbSector
Case 1
strSQL = strSQL & " AND Sector1 = True"
Case 2
strSQL = strSQL & " AND Sector2 = True"
Case 3
strSQL = strSQL & " AND Sector3 = True"
End Select
End If
strSQL = strSQL & " GROUP BY ProjectID ORDER BY ProjectID;"
MsgBox strSQL
OpenReport strSQL, chkDatasheet
End Sub
Thanks in advance!
DM1968
hi.
first of all, i am glad to join this lovely forum. i've read some threads, people here are very very helpful..
now the question (please bear with me if it's too basic, i am relatively new to Access):
i've created a 'select' query which does everything for me, except one thing - it doesn't mark one case for a certain group of records. here is the excerpt of how it looks, the last column being the one i can't figure out how to do:
Prop Date Category ID ID_unique
x1 2/3/08 001 2351 1
x1 2/5/08 001 2351 0
x1 2/6/08 001 2351 0
x1 2/9/08 001 2351 0
x1 2/3/08 001 2352 1
x1 2/4/08 001 2352 0
x1 2/3/08 001 2354 1
x1 2/4/08 001 2354 0
x1 2/8/08 001 2354 0
so for each group of records with the same ID, i need to mark only one of them.
is it possible?
thanks.. l
In have a table which contains customer address details.
I simply want to display a 2-column list. the first column
to display a disnct list of city's from the main table.
the corresponding column must give the no. of records that
contain the entry in the first column.
eg if the main table had 5 customers that lived in london
and 6 in Maidstone then
London 5
Maidstone 6
etc
I used a macro to record the date and time the current record was modified and show that information on a form - the information on how I did that is here: http://office.microsoft.com/en-us/a...0345351033.aspx
That works wonderfully, however I would like to add to this the user that modified the record.
Is there a macro code for this??
I tried setting the Item to read [UserModified] and the Expression to be User() however that did not work.
Any help would be greatly appreciated...
thanks.
My Database is really simple, it contains 1 table (called Querys) and a form called Dashboard.
In my main form (Dashboard) I have inserted a sub form which shows all the records in my table (called Querys) when I use my main form and add a record this sub table can then be refreshed and it shows all my records.
This part is fine,What I would like to do is be able to select a record in my sub form and it show the record data in my main form. This would really speed up the navigation process as the database gets bigger.