Ok... the product of all the recent question asking =D

[Woodman650]

Hey all,
Thanks so much for helping out with all my questions in the past few days (I feel like I've been asking a lot). So, I'm pretty much done with this database project, but I've got a few minor snags... 3 actually.

1) get error when use delete button and select any option other than "yes"
2) my listbox selection does not load the record in the main subform area as it is supposed to (but I think I just don't know how to set this one up)
3) the frmAdd does not allow user to enter values into the fields for some reason, don't know what's up with that one

If anyone could take a peak, check it out, let me know if there is anything I need to fix, or more importantly, how to get those above things sorted out. It would be greatly appreciated. thanks in advance =D

http://www.thepixelsink.com/other/demo.zip

Woody

 

[Hakala]

Not sure how much help this will be, but here's my comments.

1. You need error handling code on the button:
Private Sub DeleteMovie_Click()
On Error GoTo Err_DeleteMovie_Click

DoCmd.RunSQL "Delete from " & _
"tblMovies " & _
"where Title = """ & Me.lstList & """"
Me.lstList.Requery
Me.lstList = ""

Exit_DeleteMovie_Click:
Exit Sub

Err_DeleteMovie_Click:
MsgBox Err.Description
Resume Exit_DeleteMovie_Click
End Sub

(If you want the message box not to pop up, you need to turn the warnings off, too.)

2. The link between Main form and Subform cannot be made if one of them is unbound. In your demo.mdb, the Main form is unbound. I'd recommend binding it and then using the Link Child Fields and Link Master Fields properties to link the two forms by movie title.

3. The form to Add records comes up when the Add Movie button is pushed, but it is also still open in the subform area of the main form. I would think that is the problem. To get around it, I'd have the Add Movie button also close the main form, and then have the Close button on the Add Movie form reopen it.

Good luck!

 

[Woodman650]

ahhhh, Hakala thanks for checking it out. I'm sorry, I'm really new to Access. Can I ask you a couple of questions about your responses? I got #1 taken care of, thank you.

2) How do I "bind" them? I couldn't find much in the access help docs.

3) There is a different form open as the subform called "frmSummary"... it's not the add form. I was talking to someone last night that said something about my query source not updatable... but I have no idea what they were talking about.

Thank you again for taking the time to help out, I really appreciate it =D

 

[Hakala]

2. Check out this FAQ: faq702-4933 (I hope that links. If not, try searching the FAQs for "subform.")

3. I see now they are two different forms. I haven't successfully been able to make it add a record yet, though. I'll work on it today and see what happens.

 

[Hakala]

Okay, I got the Add Movie form to work. However, when I'm back on the Main form I can't tell because that one is not linked correctly, and there are no navigation buttons so I can "move last" and check it.

I added:
Code to the Add Movie button on the Main form:

Private Sub Add_Click()
DoCmd.Close
Dim stDocName As String
stDocName = "frmAdd"

DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
End Sub

Code to the Close button on the Add Movie form:


Private Sub CloseAddForm_Click()
On Error GoTo Err_CloseAddForm_Click


DoCmd.Close
DoCmd.OpenForm "frmMain"

Exit_CloseAddForm_Click:
Exit Sub

Err_CloseAddForm_Click:
MsgBox Err.Description
Resume Exit_CloseAddForm_Click

End Sub

Changed the Add Movie form to work from a query rather than a table/field selection. Here's the query SQL:

SELECT tblMovies.Title, tblMovies.Year, tblCast.Cast, tblMovies.Genre, tblMovies.Director, tblMovies.Duration, tblMovies.Plot
FROM tblCast INNER JOIN tblMovies ON tblCast.CastID=tblMovies.CastID;


And finally, changed the recordset type on the Add Movie form to
Dynaset (Inconsistent Updates)

 

[Woodman650]

hey hakala, whoa, thanks. Let me try and link up the mainform and then try getting the addform to work. I'll post my results. and thanks for all of your work thus far =D

 

[Woodman650]

I just don't get it... how it could be so difficult to link the subform to display the data associated with the listbox selection. grrrr

 

[Woodman650]

I found some code and modified it to suit my project:

Private Sub lstList_AfterUpdate()
'Moves Record Selector to record in subform that corresponds to the 'selection in this combo box.

Dim varName As Integer

varName = Me.lstList

With Me.frmSummary.Form
.RecordsetClone.FindFirst "FieldName = " & varName
If Not .RecordsetClone.NoMatch Then
.Bookmark = .RecordsetClone.Bookmark
End If
End With

End Sub

but I can't get it to work...

 

[Woodman650]

AHAHA! I got it. omg, I knew it was super simple. lol. Ok Hakala... I used Method 3 on the microsoft site. super simple... I knew it was. =D thanks! let me try and figure out that third question.

 

[adalger]

Is this form used only for addition of new records? Is the Data Entry property of the form set to Yes?

TMTOWDI - it's not just for Perl any more

 

[Woodman650]

yup. I got it working to! I am feeling good right now. =D I have one problem though...

If a user entered data and didn't hit the "add" button, the data would be written anyway. So I added a feature that set all field values to "" so no data would show up when they close the form. However, a record is still written, just with blank fields. Any idea how to discard or delete the current entry if the user doesn't hit "add" and just closes the window?

 

[adalger]

Private Sub cmdSave_Click()
    DoCmd.Save
    DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub Form_Close()
    If Not Me.Recordset.EOF Then Me.Recordset.Delete
End Sub

TMTOWDI - it's not just for Perl any more

 

[Woodman650]

and I just add that onto my "close" button? or do I put that instead of the current code? thanks Adalger!

 

[adalger]

You put the first part in your save button code (I assumed a save button named "cmdSave"), and you put the second part in the OnClose event. That way it also covers the user closing out with the "x" button.

TMTOWDI - it's not just for Perl any more

 

[Woodman650]

ohhh geez. ok, the add form is completely broken, hahaha. Ok, so here are the problems I'm encountering...

1) still can't get the exit command to work properly (discard current information in fields and close form). I also get a blank entry in the listbox from this action. here is the code on that button:
Private Sub Closefrm_Click()
On Error GoTo Err_Closefrm_Click
' Clear all search items
Me.txtTitle = ""
Me.txtYear = ""
Me.txtCast = ""
Me.txtDirector = ""
Me.txtDuration = ""
Me.txtPlot = ""
If Not Me.Recordset.EOF Then Me.Recordset.Delete
DoCmd.Close
FrmMain.lstList.Requery
Exit_Closefrm_Click:
Exit Sub
Err_Closefrm_Click:
MsgBox Err.Description
Resume Exit_Closefrm_Click
End Sub

2) when I hit "add" (save), it adds the data to the table, but cannot be viewed in my subform later on. here is the code on the button:
Private Sub AddRecord_Click()
Form.Requery
End Sub

Those are the two big issues. I can post an example file later today if people want to take a better look. the help is really appreciated. thanks again

 

[Woodman650]

and yes, the add form is set for "data entry" mode =D

 

[adalger]

Private Sub AddRecord_Click()
DoCmd.Save
DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub Form_Close()
If Not Me.Recordset.EOF Then Me.Recordset.Delete
End Sub

Private Sub Closefrm_Click()
DoCmd.Close
End Sub

TMTOWDI - it's not just for Perl any more

 

[Woodman650]

that's what I'm trying... and when I hit "add", no record is added. however, if I continue on, and try and add another, that one IS recorded. So the first record I try and add is not added.

 

[Woodman650]

is there a way to requery the listbox when closing the form as well?

 

[adalger]

1) Did you delete all the code you had for AddRecord_Click() and Closefrm_Click(), and replace it with what I posted?

2)

Private Sub Closefrm_Click()
    Forms("[i]Name_of_form[/i]").lstList.Requery
    DoCmd.Close
End Sub

TMTOWDI - it's not just for Perl any more