One-to-many problem, multiple row per record

[lostwithaccess]

Star! That's got it, thank you very much! That has had me stuck for ages.

Out of interest and for future reference, at what stage does that go wrong? Can Access pick the wrong fields or is that as a result of something I did wrong at some point?

 

[jzwp22]

I found this in the help section (searched on subdatasheet):

Microsoft Access automatically creates a subdatasheet in a table that is in a one-to-one relationship (one-to-one relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of one, and only one, record in the related table.), or is on the "one" side of a one-to-many relationship, when the SubdatasheetName property of the table is set to Auto. A relationship is defined by matching primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.) and foreign key (foreign key: One or more table fields (columns) that refer to the primary key field or fields in another table. A foreign key indicates how the tables are related.) fields in the related tables.

I'm guessing things might have gotten messed up if you changed the table structure between these tables at some point after Access had already established the relationship, and Access did not make the corresponding update (not sure if it will) with the revised table structure.

I rarely even look at the subdatasheets. I rely almost exclusively on forms.

 

[lostwithaccess]

After adding data to my database, I am now finding the need to edit this occasionally (if info gets wrongly entered etc) but presently I don't have a way to do this without finding the table and record, then entering the data there.

I have tried making a copy of my data input form, then adding a combo box in the form header which displays the last names from my staff table. I adapted some code and entered it in AfterUpdate:

Private Sub Combo4_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.Combo4) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[pkStaffID] = " & Me.Combo4
If rs.NoMatch Then
MsgBox "Not found"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

The problem is it finds some records for last name from my staff table, but not others, and it is also not populating every part of my form (I have 4 tabs to clear all the data up). I cant find the reason for this, any thoughts or suggestions?

If you think there may be a better way to search for and edit existing records I am open to any alternative methods.

 

[jzwp22]

Since the combo box's bound field is the primary key field, you will only find 1 record since the key field is unique. Furthermore, the rs.FindFirst only finds the 1st record that meets the criteria.

rs.FindFirst "[pkStaffID] = " & Me.Combo4


What is the row source of the combo box?

It the data within a field is incorrect (in the same way) for several records, it may be quicker to run an update query rather than editing each individual record. For example, let's say field1 in table1 has a value of wizrd, but you really want it to say wizard, you can run an update query to make the correction

TABLE table1 SET field1="wizard"
WHERE field1="wizrd"

 

[lostwithaccess]

The row source of the combo box is:

SELECT tblStaff.pkStaffID, tblStaff.txtLastName, tblStaff.[txtLastName] FROM tblStaff ORDER BY tblStaff.[txtLastName];

I now see how you mean it will only pull one record, is there a way to pull all related records?

Unfortunately because the data I may need to edit is specific to each record rather than being across the board I cant use a query.

 

[jzwp22]

You can use an unbound form with a text box that in turn can be used to filter your main form on open.

Create an unbound form (i.e. not bound to a table or query). Put a textbox control on the form. Now, using the command button wizard select form operations-->Open form-->select your form from the list-->select "open the form and find specific data to display"-->select your textbox control on the left and the last name control on the right, click the <--> to make the link. You will have to type the name exactly.

Alternatively, you could use a combo box with just the last names in it in place of the textbox. The row source of that combo box would be as follows:

SELECT DISTINCT tblStaff.txtLastName FROM tblStaff ORDER BY tblStaff.txtLastName;

 

[lostwithaccess]

I tried both a textbox and combo box with the command button but I am running into the same problem with both methods - after 'open form and find specific data to display' the textbox/or combo box is not appearing in the list, so I have an empty list on the left, but a fully populated list on the right, so I am unable to match up the two.

 

[jzwp22]

Are you able to post the database with any sensitive data removed?

 

[lostwithaccess]

Zipped file attached, it is empty with one example record run through it. All data entry is through frmStaffMAIN

 

[jzwp22]

I only have Access 2003 here at work, so I'll have to take a look at it this evening.

 

[jzwp22]

You had the data entry property of your staff form set to yes which opens the form ready to enter a new record.

I created the search form and modified the data entry property of the staff after the form opens. I did this in the code behind the button on the search form. The database is attached.

 

[lostwithaccess]

Thanks jzwp22, it is opening the right form with a record in, but I tried the code with more than one staff detail and it is just bringing up the same first record each time.
Do you know how I could change this?

 

[jzwp22]

You need to scroll from one record to the next. Take a look at the record selectors at the bottom of the form and you should see 1 of x (filtered). The "filtered" indicates that you only have a subset of all of the records which is what you were after. If you want to see all of the matching records in one view, you have to use a datasheet or continuous form view, but then you will not be able view your subforms.

 

[lostwithaccess]

It is showing as unfiltered at the bottom of the page, however when I go to advanced > advanced filter, the right filter is there. I tried changing to filter on load but that wouldn't work either.

There was the original "example" record, and I added "example2". When I select 'example2' from the combo and hit find by last name, it is still displaying 'example' record, and says unfiltered at the bottom of the record, although a filter for example2 is in place....

Although I am very new to coding, what you wrote does make sense to me, which is making it even trickier for me to get my head around the problem!

 

[jzwp22]

I'll have to take another look at it tonight unless someone on the forum with Access 2010 could take a look at the earlier attachment.

 

[jzwp22]

I was able to finally get your form to work. I had to change the data entry property to no directly in the form's properties rather than via code. The corrected DB is attached.

I also noticed that your institution association form is not linked properly to the main form. When I tried to relink it, it gave an error. I ended up recreating the form and setting up another staff form (frmStaff) with the new subform.

 

[lostwithaccess]

Thank you, that seems to have done the job. I have been trying to make a forms to allow the user to choose the report they want to view, with the idea being that once chosen and open they can apply whatever filter they may need.

By using code and directions from http://allenbrowne.com/ser-19.html I have now got a form which works along the right lines but I want to get rid of the checkbox to print function, but I cant work out how to get rid of this bit of code yet still have the listbox and cmdOpenReport working. All I want to have is a listbox of reports and a command button to open the selection. My cbo code and module code is below:

Private Sub cmdOpenReport_Click()
' Purpose: Opens the report selected in the list box.
On Error GoTo cmdOpenReport_ClickErr
If Not IsNull(Me.lstReports) Then
DoCmd.OpenReport Me.lstReports, IIf(Me.chkpreview.Value, acViewPreview, acViewNormal)
End If
Exit Sub

cmdOpenReport_ClickErr:
Select Case Err.Number
Case 2501 ' Cancelled by user, or by NoData event.
MsgBox "Report cancelled, or no matching data.", vbInformation, "Information"
Case Else
MsgBox "Error " & Err & ": " & Error$, vbInformation, "cmdOpenReport_Click()"
End Select
Resume Next
End Sub


Module:

Option Compare Database

Function EnumReports(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
' Purpose: Supplies the name of all saved reports to a list box.
' Usage: Set the list box's RowSourceType property to:? EnumReports
' leaving its RowSource property blank.

Dim db As Database, dox As Documents, i As Integer
Static sRptName(255) As String ' Array to store report names.
Static iRptCount As Integer ' Number of saved reports.

' Respond to the supplied value of "code".
Select Case code
Case acLBInitialize ' Called once when form opens.
Set db = CurrentDb()
Set dox = db.Containers!Reports.Documents
iRptCount = dox.Count ' Remember number of reports.
For i = 0 To iRptCount - 1
sRptName(i) = dox(i).Name ' Load report names into array.
Next
EnumReports = True
Case acLBOpen
EnumReports = Timer ' Return a unique identifier.
Case acLBGetRowCount ' Number of rows
EnumReports = iRptCount
Case acLBGetColumnCount ' 1 column
EnumReports = 1
Case acLBGetColumnWidth ' 2 inches
EnumReports = 2 * 1440
Case acLBGetValue ' The report name from the array.
EnumReports = sRptName(row)
Case acLBEnd
Erase sRptName ' Deallocate array.
iRptCount = 0
End Select
End Function

Any help would be greatly appreciated, many thanks.

 

[JANR]

DoCmd.OpenReport Me.lstReports, IIf(Me.chkpreview.Value, acViewPreview, acViewNormal)

Just remove the bold part so it just prints which is the default of the second argument of DoCmd.OpenReport. If you want to always preview all reports before your print then use this instead:

DoCmd.OpenReport Me.lstReports, acViewPreview

JR

 

[lostwithaccess]

Thanks JR, that did the job perfectly

 

[lostwithaccess]

I have created a query and report, which I now want to be able to filter from a form, which needs 3 combos to select. I have found the microsoft support example, which I have been trying to modify, but I just cannot get it to work. I get the error "Run-time error '2465' Microsoft office access can't find the field 'Filter1' referred to in your expression'. When I go to debug it highlights the following:

Me("Filter" & intCouter) = ""

From other forums I have seen a suggestion that changing the Tag property can help, which I have tried but with no luck, I am in a bit deep when it comes to coding. Here is my code for onOpen, any help would be great.

Option Compare Database
Option Explicit

Private Sub Set_Filter_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 5
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![rptInstitutions].Filter = strSQL
Reports![rptInstitutions].FilterOn = True
End If

End Sub


Private Sub Command2_Click()

Dim intCouter As Integer

For intCouter = 1 To 5
Me("Filter" & intCouter) = ""
Next

End Sub



Private Sub Close_Click()
'DoCmd.Close acForm, Me.Form.Name
DoCmd.Close
End Sub

Private Sub Form_Close()
DoCmd.Close acReport, "rptInstitutions"
DoCmd.Restore
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenReport "rptInstitutions", acPreview
DoCmd.Maximize
End Sub