Reports are showing errors in the data fields

Hello all,



I have a report (upholstery orders) that shows times, quantities and orders details ect. I open this report from a dashboard navigation form using a button (easy enough).

The report opens fine, all data fields/box's/records show up fine and accurate. I have written macro's so that when certain fields (Sales and Works order numbers) are clicked they open another report displaying the necessary information based on the selected fields Data. This again opens fine (no drama's), however, if I then close that form or simply return to the original report (upholstery orders) using the tabs, various fields/records containing data now show "#Error" (which is a slight pain).



If I close the original form and open it again the errors naturally disappear for a while until I move away from it again. I understand there is more pressing and more imaginative problems regarding Access, as it's simple enough to close and reopen, I just wondered if there was any error or requery based solution I can apply to my report, in order to save my colleagues some of that most valued theoretical commodity, time.



Thanks in advance.

Reports are showing errors in the data fields

Hello all

Used to look after a number of Access 2000 and 2007 desktop databases in my last role some 3+ years ago.



Now getting in to Access 2013 Web Apps on SharePoint 2013 which so far appear to be a very different kettle of fish so may well see you over in the Web App forums :)

Hello all

enabling cascading comboboxes in datasheet or continuous form

Having been asked about this recently, I reinvented some code I developed to work with datasheets and which also works with continuous and single forms.



The attachment was developed in 2010 but saved as 2003.



The db demonstrated two techniques for using cascading comboboxes.



Technique 1 is the simpler one and is used where the combobox displays the bound column after updating



Technique 2 is slightly more complex and is used when the bound column is not displayed after updating - which is frequently the case. However it can also be used in place of technique 1



Note that all the comboboxes need to be bound (in order to retain the values selected for each record). However if used in a continuous/datasheet form in dataentry mode or a single form, they do not need to be bound.

Attached Files

ContinuousCascadingCombo.mdb (448.0 KB)

enabling cascading comboboxes in datasheet or continuous form

Inserting a function within a sub in VBA

I need help fixing this code. I am new at VBA in access and I am working on an application I didn't develop but I am maintaining.

Code:

---

Private Sub Command122_Click()

Dim formName As String



formName = getFormName(tblName)



DoCmd.OpenForm (formName)

DoEvents

Forms(formName).FilterOn = False

Forms(formName).Combo94.Value = (myreference)

Forms(formName).Filter = "DeptID = '" & Forms(formName).Combo94.Value & "'"

Forms(formName).FilterOn = True

Forms(formName).Controls(fieldName).SetFocus

DoCmd.Close acForm, Me.Name

End Sub







Public Function myreference() As String



SELECT ID FROM tblSiteInfo WHERE ( DeptID = " & DeptID & " )

End Function

---

I am trying to to write afunction that translates the deptid into a table 'id (myreference)



Thanks in advance

Inserting a function within a sub in VBA

[SOLVED] LOG AND CODE Formula

Dear All Expert MS Access



With regard,

in MS Access I have a table and want to make a code to enter the formula in the query, but I have not used multiple functions into one

as an example of a query in addition to its



please would anyone can help



thank you



Regards, Ira Borneo

Attached Files

LOG AND CODE Formula.xls (33.5 KB)

[SOLVED] LOG AND CODE Formula

Add a filter to a subform

I am trying to apply a filter to a subform but don’t know how to go about it.

I have three tables as follows:





ITEM (primary key Item ID) (AutoNumber)





CREATOR (Primary key Creator ID) (AutoNumber)





ITEM CREATOR (junction table) (primary key Item ID and Creator ID)





ITEM has a one to many relationship with ITEM CREATOR.

CREATOR has a one to many relationship with ITEM CREATOR.





I have added a subform to the data entry form for ITEM. The fields in the subform are the fields in the ITEM CREATOR table, i.e. Item ID and Creator ID. The purpose of the subform is to link an Item to a Creator and populate the ITEM CREATOR table.





These are the subform properties:





Record Source: Item Creator subform

Link Master Fields: Item ID

Link Child Fields: Item ID





The subform contains a combo box for Creator ID. These are the properties.





Control Source: Creator ID

Row Source: Item Creator Subform query

Bound Column: 1

Column Count: 9





When a user clicks on the combo box drop-down, the first 9 fields in the CREATOR table are displayed. The purpose of this is to give the user more information when linking ITEM to CREATOR, so that the correct Creator ID is chosen and the Item is linked to the correct Creator. Two of these fields are Creator Surname and Corporate Creator.





This works well.





The issue is that CREATOR now contains over 2500 records. When trying to link an Item to a Creator the drop-down list that appears when the Creator ID combo box is clicked is very long and the user is potentially faced with a long time spent scrolling the list. This is not very user-friendly.





I would like to filter the drop-down list by Creator Surname so that the user enters a surname and only the Creator IDs for the Creators that have that surname appear in the list.





In addition, I would like to filter the list by Corporate Creator.





I hope that the above makes sense. I would much appreciate any help.

Add a filter to a subform

Help with Queries (Macro)

First off I apologize for the big “wall of text” and I am no expert and self-taught.



I am here not so much with a problem as a question, is the process being done correctly and/or is there a more efficient way of doing it. The process I am using works it just seems clucky.



The attached Access 2013 database deals with training events. I have remove most of the database components that are not relevant to this issue. All of the data is fictitious, names created using a random name generator. All references to training courses, button or links do not work as their underlying queries and tables have been removed.



Training events are events attended by more than one person and an event roster or signup sheet is typically used, in fact I have created a report to generate this signup sheet. Training courses are done by one person, they typically are online courses and a certificate of completion is generated.



Originally I had training events and courses in the same table using the queries and the like. I realized there was an issue, when it came time to enter the attendees into their individual record I had to do it one at a time, and if you had 50/75 or more people in attendance this created a labor/time intensive task.



So I broke the events and courses into two separate tables with their own supporting queries, forms and reports.



Unlike courses, events for the most part don’t change, when they happen and who attended a particular event does. So over a year you could have an event say Fire Extinguisher Training occur several times.



If you look at the Relationship section/tab you will find four tables tblEmployee, tblTrainingEvent, tblTrainngEventLink and tblAttendanceRoster along with the relationships. The objective is to add a record in the tblTrainngEventLink table for each person that attended a give event. I have accomplished this using a macro with a series of update and append queries and a statement that deletes all of the records in the tblAttendanceRoster table. The tblAttendanceRoster table is used as a temporary hold place data until it is appended.



From the Main form (switch board) select the Training Management and Reports button, on this form you will find button related to courses these do not work as their under lying tables and queries were removed. Select the Create/Edit/View Training Event button. This brings you to the Training Event List form.



From the Training Event List form, you can see all of the training events, the event name, type, event date, attendance roster and notes section. Associated with each event you will two buttons, the Attendance Signup Sheet button that generates a signup sheet report containing all of the employees, along with the event information and a box for their initials. The attendance button takes you to the TrainingEvenrt Attendance Roster from. This form contains the event information in the upper section (training event ID is hidden) and a listing of all employees with a check box. Once the event is completed you would use the signup sheet and check off all employees that attended.



Once the boxes are all checked you would select the Process Training Event Roster button, this activates the follow macro.



As you check the boxes to indicate a person has attended a record is added to the tblAttendanceRoster table with employee ID, the first step in the macro is a Requery to ensure everyone is accounted for. Next an update query, this updates the tblAttendanceRoster records with the training event ID. Next an append query takes the Employee_FK and the TrainingEvent_FK and appends them to the tblTrainngEventLink, thus linking all of the employees attending to a given event. The remaining steps remove (delete) all of the records in the tblAttendanceRoster table.



This process works but as you can see it is a bit clunky. Now I know you can turn off the need to acknowledge the update and append queries however as far as I know you can’t do this for the delete actions. I would like a way to have this done “behind” the sign without having to turn off the query acknowledgements. Also more to the point, is the process I am using with the tblAttendanceRoster table being a temporary table the way to go. Is there a better more efficient way.



As stated I am no expert and self-taught,



Thanks in advance.

John

Attached Files

EmployeeFoldersTest.accdb (1.81 MB)

Help with Queries (Macro)