Main form w/ 2 subforms fails to open current record

[ricoriv]

Hello everyone, I have a main form "frmDriverNoticeRecord" with 2 subforms, "frmSubDriverNotice" and "frmSubVehicleRecord"
The main form uses "IncidentID" as the PK. Each form stores data in its own table. The tables are related via IncidentID and there is a 1-many relationship between tblIncident (main) and tblDN (sub) and tblVehicle (sub).
I have a form "frmIncidentLog" displaying records in a table view with a few fields for quick reference and to allow selection of a single record for detailed viewing. I created a command button with the intent to open the "frmDriverNoticeRecord" at the current record.
The main form and child link fields appear to be linked correctly; i.e. IncidentID on all three. If I open the form manually it opens and I can use it and search and filter as I want. If I try to open it via the command button It opens a small window asking for the IncidenID, when I enter theIncidentID number, it opens the form to the first record every time.
Here is the code:

Private Sub comOpenDR_Click()

Dim frmName As String
Dim recID As String

frmName = "frmDriverNoticeRecord"

recID = "[IncidentID]=" & Me![IncidentID]

DoCmd.OpenForm frmName, , , recID

End Sub

I tried running the the DoCmd.OpenForm command directly from the button using the where condition (in various manners) with the same results.
Please, any help would be appreciated.

 

[pbaldy]

If you're getting a parameter prompt, Access can't find whatever it's asking for. Make sure the field name is spelled correctly and is present in the record source of the form being opened.

 

[ricoriv]

Thank you so much for the quick response. I've been knocking my head on teh wall since last Friday.
The field name is spelled correctly and the field is present on the main form and subforms. The main to child links are set.
I use this little block of code on several other forms and it works.
The only difference I can see is in the main to child link dialog box, the main field is listed as tblIncident.IncidentID and the child fields are just IncidentID for both. Could this be a problem?

Is it perhaps something to do with the main form having 2 subforms?

 

[ricoriv]

I've tried using:
DoCmd.OpenForm "frmDriverNoticeRecord", , , "IncidentID = " & Me.IncidentID

and

DoCmd.OpenForm "frmDriverNoticeRecord", , , "IncidentID = " & Me![IncidentID]

and

DoCmd.OpenForm "frmDriverNoticeRecord", , , "IncidentID = " & Me.ActiveControl

They give the same result, asking for the IncidentID number. When teh number is input, it opens to the first record regardless of the input number.

 

[pbaldy]

Can you post the db here? The parameter prompt implies that field isn't in the form being opened. The listing of tblIncident.IncidentID is typically seen when the field named IncidentID is included in more than one table listed in the record source, so the table name is added to disambiguate which field is desired.

 

[ricoriv]

The DB is 188 MB in size. Can I send it to another location? I have seen the table.field syntax before and the IncidentID is on all 3 forms. I even made the fields visible in case that was affecting it.

 

[ricoriv]

This form is actually supposed to be called from a combobox:
the code is:
[FONT=&quot]Private Sub cboSelectForm_AfterUpdate()[/FONT]
[FONT=&quot]Dim strForm As String[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot]If Me.cboSelectForm = "Alarm Record" Then[/FONT]
[FONT=&quot]strForm = "frmAlarmRecord"[/FONT]
[FONT=&quot]ElseIf Me.cboSelectForm = "Vehicle Record" Then[/FONT]
[FONT=&quot]strForm = "frmVehicleRecord"[/FONT]
[FONT=&quot]ElseIf Me.cboSelectForm = "Gate Request" Then[/FONT]
[FONT=&quot]strForm = "frmGateRequestRecord"[/FONT]
[FONT=&quot]ElseIf Me.cboSelectForm = "Abandoned Property" Then[/FONT]
[FONT=&quot]strForm = "frmAbandonPropRecord"[/FONT]
[FONT=&quot]ElseIf Me.cboSelectForm = "Comm Building Record" Then[/FONT]
[FONT=&quot]strForm = "frmCommBuildingRecord"[/FONT]
[FONT=&quot]ElseIf Me.cboSelectForm = "Maintenance Record" Then[/FONT]
[FONT=&quot]strForm = "frmMaintenanceRecord"[/FONT]
[FONT=&quot]ElseIf Me.cboSelectForm = "Issue Report" Then[/FONT]
[FONT=&quot]strForm = "frmSMCissueDetails"[/FONT]
[FONT=&quot]ElseIf Me.cboSelectForm = "Incident Report" Then[/FONT]
[FONT=&quot]strForm = "frmIncidentInfo"[/FONT]
[FONT=&quot]ElseIf Me.cboSelectForm = "Driver Notice" Then[/FONT]
[FONT=&quot]strForm = "frmDriverNoticeRecord"[/FONT]
[FONT=&quot]End If[/FONT]
[FONT=&quot]If Me.Dirty Then Me.Dirty = False[/FONT]
[FONT=&quot]DoCmd.OpenForm strForm, , , "IncidentID=" & Me.IncidentID[/FONT]
[FONT=&quot]Me!cboSelectForm = Null[/FONT]

[FONT=&quot][/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]Every form is called correctly except the frmDriverNoticeRecord, this asks for the IncidentID, etc.
[/FONT]

 

[ricoriv]

Information only,:Like many, I am self taught, I'm always learning. I do not get insulted by corrections.

 

[pbaldy]

That's a big db, unless it has a lot of data in it. Does doing a compact/repair and then zipping bring it down to size?

 

[ricoriv]

We have been using it since Jan 2014, currently Approx. 2600 records some with file attachments. I recompile at least once a month.

 

[ricoriv]

I will try zipping it tomorrow.

 

[ricoriv]

Ok, I found the problem, the form's data source is a query that gets its data from 3 tables. The main table "tblIncident" has a 1 to Many relationship with "tblDN" and "tblVehicle". tblDN and tblVehicle are not related. this is causing the query to be Un-updatable. How this keeps the form from opening to the current selected record is lost on me. but I know I have a problem.
Now to work on making the data source (qryDN) updatable.

 

[pbaldy]

Shouldn't matter that the query is read only.

 

[ricoriv]

Here is a solution, though it wont work for me because I have existing records.
Change the relationship for tblIncident and tblDN from 1 to many to 1 to 1. Once I made that change the query was updatable and the form responded to a DoCmd.OpenForm. Now, how do I enter the 48 existing records without having to create new records since the 1 to 1 relationship is unique? I guess that is a discussion for for another thread.
Thank you pBaldy for your help.