I have a combo box and button on a form that should open another form and display the results in a datasheet view filtered by the combo box selection. The second form is based on a query that has the following criteria in the JobType field:
Code:
Form!frmReportView!cboType
When I run the query it correctly prompts me to enter a value for the criteria and displays the proper results. Likewise, the same thing occurs when I run the second form independently. The problem is when I try to run it with the combo box and button. The second form opens in a datasheet view with the headings, but no detail records are being displayed.
On it, there is a subform called 'patient' - this has a number of subforms in the detail section (Linked on a one-to-one key).
When the patient form loads, I hide the detail section until a user either
A. Finds an existing client record or B. Clicks the 'ADD NEW' button
The ADD NEW button opens a separate (pop up) form where the primary patient information is gathered. When the user clicks "Save" on the pop up, my VBA script ...
A. Creates all the one-to-one relationships that are required. B. Updates the 'Find Patient' field to the newly created patient number C. Finds the new record D. Un-hides the detail section (This is what I cant get to work)
The rationale for hiding the detail section in the first place? The answer is twofold.
1. If the user simply creates an new patient, the three actions (A, B C above) don't always run/display the data properly. (Im not sure why? ) The pop up form seems to be a good working solution for me. 2. My users have a tendency to change data on the default patient. I have tried going to new record, but then they add new (often duplicate) patient records.
I have a main form with 3 subforms. Each subform is identical except for the value of the filter property. The filter is for the same field, but with a different value for each subform. So, for example, the first subform has a filter of:
Code: [WBS Element]="DEF" And [Period]=Forms!frm_ProjectFinancials!Period
while the second subform has a filter of:
Code: [WBS Element]="PPE" And [Period]=Forms!frm_ProjectFinancials!Period
and the third subform has a filter of:
Code: [WBS Element]="EXP" And [Period]=Forms!frm_ProjectFinancials!Period
The recordset for each subform results in a single record with numeric values in each field or no records at all. When the resulting recordset is empty (no records), the bound text fields on the subform display as blank. I want these fields to display 0 instead of blank so I can use them in other calculated fields. Functions such as Nz or IsNumeric do not work since there are no records and the values are neither null nor numeric.
How I can display zeroes in the bound fields when no records exist that meet the filter criteria? Or is there a way that I can dummy a resulting recordset to have all zero values when there would otherwise be no records?
This is an ongoing problem I have had for 4 weeks now.
I have made a a system thats acts like a clock In/clock out Out system.
the structure is somthing like this ID Username tblDailyLog TimeIn MorningBreakOut MorningBreakOut LunchOut LunchIn AfternoonOut AfternoonIn TimeOut
All fields apart from ID (autonumber) and username (String*255) are Date field (there are a few others like DateOfTimesheet etc but they arnt important here)
When a user arrives in the morning they make a record which they use for the day
They then have a form with a whole bunch of buttons which simply updates the correct field. For example they click the "Sign in for the Day" button and it updates the correct field with the current time.
Everything was going fine until people noticed that every now and again a sign in time dissapeared.
I have hacked myself to death trying to solve this problem but still the updates go Astray.
Now each time a time is updated the process goes somthing like this
1. the user opens their timesheet for the day (the RS is SNAPSHOT and no locks)
2. User Hits a sign in/out button 3. The record source is changed to "" and all buttons hidden (to ensure the record isnt locked and to make sure you dont do two things at once) 3. The table is updated with the new time (using some dynamic SQL) 4. The table is repeatadly checked using a DO loop to make sure the the correct time went in. 5. when the returned time value of the field matches the varaible used to update it, the form is returned to normal and the user carries on his/her merry way (if it never matches the screen should crash but this never happens). 6. A New record is added to another table called "tblbugfixinglog" which records which field was updated and when. This is so that I have two records in two different ways (figured if one went astray I could pull it back off the other) 7. Another new record is added to yet another table called tblSQLRecord, which simply logs all .RUNSQL statements that are executed.
I thought that the two extra tables (and the check that the record had been updated) would help me track down where the records are going missing, but this isnt the case.
Now it appears that some records arnt being added to tblBugFixingLog and to tblSQLRecord either and some of these tables are getting quite a few #ERROR's in them..
None of the tables are related to any other and i've no idea how #ERROR lines are appearing in a table that has 1 function... to recieve new records ... no editing, no viewing, no deleting.
Does anyone have any idea how these updates/inserts can go missing or create #ERRORs. I've built plenty of Databases in my time and have never come across this. __________________________________________________ ______________
This is the function I use to add a record to tblBugfixingLog and tblSQLRecord
Private Sub AddBugLog(ByVal TimesheetNumber As Long, ByVal FieldUpdating As String, ByVal NewFieldValue) Dim TempSQL As String TempSQL = "INSERT INTO tblBugFixingLog (TimeAndDateOfEntrySERVER,TimeAndDateOfEntryPC,Fie ldUpdated,NewEntry,UserID,TimesheetNumber,Computer AssetNo) VALUES (" & _ "#" & Format(ServerGetTime(Environ$("LOGONSERVER"))) & "#," & _ "#" & Now & "#," & _ "'" & FieldUpdating & "'," & _ "'" & NewFieldValue & "'," & _ "'" & GetNTUser & "'," & _ "'" & TimesheetNumber & "'," & _ "'" & fOSMachineName & "')" ' MsgBox TempSQL DoCmd.RunSQL "INSERT INTO tblSQLRecord (Username,DateAndTime,Screen,TheSQL) VALUES('" & LoginInfo.sUsername & "','" & CStr(Now) & "','Add Bug Log function','" & CleanData(TempSQL) & "')", False 'CleanData is a function that removes ' and " from the SQL string so i can easily add the SQL string into the table DoCmd.RunSQL TempSQL, False End Sub
Public Function CleanData(ByVal DataToClean As String) Dim TempData As String Dim i As Integer TempData = "" For i = 1 To Len(DataToClean) Select Case Mid(DataToClean, i, 1) Case "'" TempData = TempData & "`" Case """" TempData = TempData & "`" Case Else TempData = TempData & Mid(DataToClean, i, 1) End Select Next i CleanData = TempData End Function
The first report example is what I get when I start on the main form and select an organization, run the report, opens report - BIG SPACE between the details and my footer section which holds a subform.
The second report is what I need it to look like, what's strange to me is I get the report correctly when I just run the report (I don't pass the organization into the report from the main form)
I tried changing the background color of the detail section and the footer section to figure out where my problem is but the space just stays white.
The problem I am facing is applying an IF statement to every record in the detail section of the subform.
I have the following code:
Code: If Me.status = "CONFIRMED" Then Me.course_ref.Enabled = False Me.course_date.Enabled = False Me.cmbModule1.Enabled = False Me.cmbModule2.Enabled = False Me.course_start_time.Enabled = False Me.course_end_time.Enabled = False Me.course_training_cost.Enabled = False End If
This is in the on load event of the subform and works 'sort of'
Basically I have a record with the status of confirmed and records without this status, but the result of the if statement is being applied to all records. Is this because I need some sort of loop? and if so how would I loop through all records in the detail and apply this if statement to them all?
I am creating a report that is organized by project. The detail lines are to list payments applied to the project. How can I skip the detail section (or print a single blank line) if there are no payments in the separate payment table that match the project ID? Is there a way to tell that there were no matching payments and format accordingly? I currently get multiple blank lines.
I have a report that contains a subreport (form) that pulls records from a query. When I go to run the report, the records are there correctly, but the subreport continues to duplicate many times.
The record source of a form that I have is based on a user selection in a combo box in the header of the form. When the form opens there may or may not be any records to display. Currently I put up a message box when there are no records displayed but this only happens when the form is newly opened.
I was wondering if it is possible to have a label displayed in the detail section instead whenever there are no records to display, such as something along the lines of "There are no records to display with the selected option, please choose an alternative.".
I realise that I may be asking the impossible but I'm a member of the "If you don't ask you'll never know" club.
I am trying construct a form that will only show an invoice if all the invoice records have been approved. I have created a form that will allow the user to look at each line of an invoice and has a checkbox to approve it. If the user has selected all the boxes it will disappear from the Approval view, which is a query that shows unique values.
The problem is that if all of the lines but one are checked it will also show up in the view for the unapproved invoices AND the approved invoices. I would like to know how to structure a query that shows unique values but ONLY if ALL of the invoice line items have been approved.
I have a main form (Parent) along with a subform(Children). I want to have a button that generates a report with the Parent information as a header and the items in the subform as details. In addition, I want the report to show only the children that were recently added not all of the children.
I am trying to create a report which basically includes the following:
Company, Wages, Contribution.
Each company reports wages for each employee every month. Then they also contribute to a general fund based on a percentage of the wages. For instance:
Each employee is required to contribute, in this example, 1% of gross wages to the general fund.
On occasion, the company does not pay in the required 1% of gross, say, for CompanyA EmployeeA, they only paid in $35.
Here is what I need to do. If any contribution amount for any employee is incorrect, I want to display all the records for that company, not just the incorrect ones. The report is grouped by Company, and may contain dozens of companies.
I am already passing a number of criteria to the report using a filter, including the date range and other fields which are informational.
I'm trying to create a report where I can use a section header as a hyperlink to show/hide detail, but only for that section. For example, my customer names are:
Code: ABC Co. ZYX Co. 123 Co.
If I click on ZYX Co., I want it to show the contracts for ONLY that customer:
Ok ive got one subform displaying details on bikes and i have 3 other subforms displaying well information on the bikes.Repairs,Hires,Other info(All calculated fields The BreakevenPrice when the bike is sold etc)these forms are identicle but ive just added frmBreakevenPrice Subform I cannot enter [frmSubBikes].Form![BikeID] as the link master field dunno if just having BikeID in the affects it. But anyway this code when it runs Me.Parent![frmBreakevenPrice].Requery It sais Microsoft access cannot find the field 'frmBreakevenPrice' Refered to in your expression.
Private Sub Form_Current()
Dim strParentDocName As String
On Error Resume Next strParentDocName = Me.Parent.Name
If Err <> 0 Then
Else On Error GoTo Form_Current_Err Me.Parent![frmSubHires].Requery Me.Parent![frmSubRepairs].Requery Me.Parent![frmBreakevenPrice].Requery End If
I'm curious if anyone know's a way to access a specific data section on a tabular form. I'm attempting to have each section update a specific data item based on its position on the form, but am having problems since I do not know how to differentiate between one details section and another on the form. Any help would be appreciated.
I have set up a split database and it seems to work perfectly. I have created a report on each users FE and this works fine. It inputs their own contact details on the report. This was done manually before sending the FE to the user just using ="text here" in the design of the form.
What I want to do is add an extra field to the table (and link to this via the form each user has) and have this populated automatically with the users name.This is so we can see at a glance which user added which record.
Is there a way to have this happen or would it be a case of having to pick your own username from a combo box? Can I do something with each users FE form which will add their name when they add a record?
I am a novice ACCESS user, so my questions may be elementary. In Access 2007, I have a query that presents the user with a list of the contacts he/she is approved to access. I want the user to be able to "drill down" to the detail form for a specific contact by double clicking on his last name, but I have not been able to find a property sheet for the last name field in the query to which I can apply the code.
I hope this is the right section for posting this Q.
I use Access 2003 on WinXP pro as front-end & for back-end a MySql on a Linux server. I use MyODBC to connect to the back-end and all the tables are on the back-end. The workstation is connected to the server via VPN (so the server and the station are on different locations).
Quite often I get a problem that not all records are inserted into a table. E.g. I have like 5 - 15 records (up to 10 fields) in one table and I want to transfer/copy them to another:
Hello people. Iam having a problem and i need some help. iam appending data in one table to data in another another. I have two tables(table1 and table2), with the same fields but the records in one table2 are 5 more than those in table1 which are 3. The 3 records in table1 are also in table2. I would like a query which outputs the other 2 records that are in table2 and not in table1.
i have a query that contains two tables one contains the member details the other their transactions. the query is to show all members and transactions however if there is no transaction then the member details do not show - -the query only shows members with transactions . the query does have grouping to give totals of the transactions
I have a bound form which shows list of items in the stock. When i click on a button it should open another form which shows the details of item which we choose from the first form.the code which i have in click event of the form is :
Code: Dim strCrit As String strCrit = "PkID=" & Me.RadStocks DoCmd.OpenForm "frmIssueRadItems", , , strCrit
It works sometimes but most of the time it gives error saying " syntax error(missing operator) in query expression 'PkID=Airmux 200E DC".
I have a query which combines several linked tables, the query has about 10 columns, I need to show only the records which have one or more empty fields.
Some records may only have one missing field others may have several.
