Opening a form from a subform

[sstasiak]

I have a form called frmListing. In this form I have 2 buttons: View and Delete. There's also a subform that shows all records from my main table[tblOncReg] and displays only the 4 most important identifying fields.

When I click a record in the subform(table), I want to then be able to click the buttons and have the appropriate action performed on the record I clicked on. Not sure what action and arguments to apply to the buttons so they focus on the selected record.

This particular form is simply a complete listing of patients in the DB from where a user can either quickly view the data in my main form, or quickly delete a series of records.

Any ideas?

 

[Oldsoftboss]

Where are the buttons you want to click? Are they on the ones main form or do you have other buttons on the subform ?

 

[sstasiak]

The buttons are on form frmListing, which has a subform which gets its data from my main table, tblOncReg. When I click and highlight a record in the subform, I want to be able to click either "Open" or "Delete" and have those actions performed on the highlighted record.

 

[DrSnuggles]

Try this little beauty . . . .
Be careful with the Delete button as the record pointer will point at the first record in the subform.
Perhaps stick a "Are you certain?" prompt for the user when deleting.

Private Sub cmdOpen_Click()
dim stDocName, stLinkCriteria as String

strDocName = "formName"
strLinkCriteria = "[fieldName]= '" & subformName![fieldName] & "'"

DoCmd.OpenForm strDocName, , , strLinkCriteria

End Sub

Private Sub Delete_Click()

Dim mstrSQL As String

mstrSQL = "DELETE * FROM tblOncReg WHERE [fieldName]= '" & subformName![fieldName] & "'"

CurrentDb().Execute mstrSQL

subformName.Requery

End Sub

-----------------------------------------------------------
I've assumed your fieldname type is text/String.
If numbers then use this:

Private Sub cmdOpen_Click()
dim stDocName, stLinkCriteria as String

strDocName = "formName"
strLinkCriteria = "[fieldName]= " & subformName![fieldName]

DoCmd.OpenForm strDocName, , , strLinkCriteria

End Sub

Private Sub Delete_Click()

Dim mstrSQL As String

mstrSQL = "DELETE * FROM tblOncReg WHERE [fieldName]= " & subformName![fieldName]

CurrentDb().Execute mstrSQL

subformName.Requery

End Sub

 

[sstasiak]

Snuggles

Unfortunately that code doesn't work. I'll give more detail.

The primary key of the main table(tblOncReg) is [MEDRECNO] which is a number.

The form OncListing contains the 2 buttons: Open and Delete. It also contains a subform, OncListingSub, which gets data from tblOncReg. It only displays 3 fields for identification: Medical Record #[MEDRECNO] which is the primary key in tblOncReg, last name, and first name.

Again, when I click a record in OncListingSub and then click Open, I want my main form, OncRegMain, to open with all fields populated by the data for the record I selected in OncListing.

Here's how I modified your code to reflect my forms and tables:

Private Sub cmdOpen_Click()
Dim stDocName, stLinkCriteria As String

strDocName = "OncRegMain"
strLinkCriteria = "[MEDRECNO]= " & OncListingSub![MEDRECNO]

DoCmd.OpenForm strDocName, , , strLinkCriteria

End Sub


AND

Private Sub Delete_Click()

Dim mstrSQL As String

mstrSQL = "DELETE * FROM tblOncReg WHERE [MEDRECNO]= " & OncListingSub![MEDRECNO]

CurrentDb().Execute mstrSQL

subformName.Requery

End Sub

 

[DrSnuggles]

Are you saying that by replacing your object names that the above code will not work??

 

[sstasiak]

That's right. As you can see, I simply copied and pasted your code, input my object names, and it didn't work. Maybe it's something with the form or subform I'm using.

The form name is OncListing with the subform named OncListingSub. I created the subform by using the subform tool in the design view. Then when it asks what data I'd like to use for the subform, I chose to use my main table, tblOncReg, and only included those fields I mentioned above: MEDRECNO, LNAME, FNAME.

 

[sstasiak]

Can anyone else see anything wrong with the code above?

 

[DrSnuggles]

What errors messages are you getting?

What about Using the menu option Debug\Compile (your module name)

 

[sstasiak]

I get a runtime error 424, "Object required" which is for line:

strLinkCriteria = "[MEDRECNO]= " & OncListingSub![MEDRECNO]

 

[DrSnuggles]

Just a quick response, as I have to shoot . . .

1.Can you check that the field [MEDRECNO] is used on the new form you open.

2. Can you check that the Control Name on your subform is "MEDRECNO" (will be the ControlSource too anyway).

If so, try this:
strLinkCriteria = "[MEDRECNO]= '" & OncListingSub![MEDRECNO] & "'"

Or could try this:
strLinkCriteria = "[MEDRECNO]= '" & Me.OncListingSub![MEDRECNO] & "'"

Tell me what happens, will have a look tomorrow.

 

[sstasiak]

1. The form that is being opened does use the field [MEDRECNO]

2. While in design view of the form, I click on the subform and highlight the entire subform. When I do this there is only a Source Object listed under the data tab. That object is the name of my subform, in this case "OncListingSub".

This confuses me a bit since my subforms record source is tblOncReg(my main table)

 

[DrSnuggles]

1.Open the your main form.
2. Right click over the subform to bring up the menu window and select "Subform in New Window" (at the bottom) - this will open the subform itself in a new window.
3. Then bring up the properties box for the text box/control that references the field [MEDRECNO].
4. Look at the "DATA" tab, the ControlSource should be [MEDRECNO], now click on the "OTHER" tab, make certain that the Name is also [MEDRECNO].

Is this the case?

 

[sstasiak]

DrSnuggles

That's correct.

 

[DrSnuggles]

Sent you a private message.