Anyone good with Database programming?

[neveryona]

Hello everyone. Currently I am taking a computer programming course,and in the middle of a huge database programming project using Access. One that I have to admit in making me pull out my hair. I am almost finished, just trying to fix the bugs. However, I keep running into problems. I get one thing working and then something else doesn't work. I am essentially developing a database for an art gallery. One where information is entered entirely through forms, for the ease of the user. I even went beyond the call of duty and developed one main form, where one can scroll through the records of each art piece and see all the information that was entered, (ie.artist, period, medium, curator). I did that by creating a query with all the required fields, one that updates as new information is entered. It was working fabulously until I changed something in my curator form, now it is not working. There are no error messages, it just isn't updating the query. Any new information goes into its corresponding tables, just not the query.In the form_afterupdate of the form that is supposed to update the query, I have written this code:
Private Sub Form_AfterUpdate()
Dim intIndex As Integer
For intIndex = 0 To Forms.Count - 1
If Forms(intIndex).Name = "frmPiece" Then
Forms(intIndex).Requery
End If
Forms(intIndex).Refresh 'refreshes all controls on open form
Next intIndex
End Sub
Does this code look right? It was working. I have racked my brains trying to figure out what happend when I made the change in my curator form...and just can't figure it out.
Anyway, hope someone can help. I have looked out there on the internet for good forums dedicated to programming, and yet to have come across one that matches you guys, in speed or quality of response. http://www.PCGuide.com/ubb/smile.gif Thanks in advance...

[This message has been edited by neveryona (edited 06-02-2002).]

[Paul Komski]

I guess one learns the hard way to keep incremental backups of a Db, while developing it.

If I have understood you.
There are 5 Tables of Data; art piece and then 4 more (artist, etc. each of which presumably has a Primary Key).
The art piece table also has 4 specific fields that hold the value of the primary key of the 4 other Tables.
There is a Select Query that links all these 5 tables together and on which your main form is based.
You then edit a record on the curator form and this change is not reflected in the main form.

1. The code looks intrinsically correct but (a) there is no need for such a For...Next since you know the name of the form and (b) there is no need to .refresh on every loop and maybe not at all. If you need the refresh method then include it within the If...End If or on a new line after Next ..
It could be written more simply, say:-

Private Sub Form_AfterUpdate()
Forms![frmPiece].Requery
Forms![frmPiece].Refresh 'Probably unnecessary to use both refresh and requery
End Sub

or more elaborately:-

Private Sub Form_AfterUpdate()
Dim frmToRequery as Form
Set frmToRequery = Forms![frmPiece] 'or = Forms("frmPiece")
With frmToRequery
.Requery
.Refresh
End With
End Sub


2. One possibility is that the code is correctly written in the form's module but in the Forms Properties the AfterUpdate Property is not actually displaying "Event Procedure" from the dropdown list and so the underlying code is not being executed.

3. Another possibility is that you haven't actually updated the curator form before viewing the frmPiece form; (is the curator form closed or otherwise saved/updated before viewing the frmPiece form?)

4. Another possibility is that the Curator's IndexField inside the Piece Table is not included in the Main Query's recordset so that if you have added a new curator record it will not update the Piece Table automatically.

5. Another possibility is that the relationships in the underlying Query should be of the appropriate type (ie show all records from the Piece Table).

6. Another... well that should be enough to start with!! Good Luck.

Edit: To check if the problem is with the underlying Query or the Curator Form place two command buttons temporarily on the Piece MainForm and then write an On Click event procedure for both.
Sub CommandButton1_Click()
Me.Requery
End Sub
Sub CommandButton2_Click()
Me.Refresh
End Sub

Then (after updating the curator form) play with them to see if either or both will now "refresh" its data.
http://www.PCGuide.com/ubb/tongue.gif

[This message has been edited by Paul Komski (edited 06-04-2002).]

[neveryona]

[QUOTE]
4. Another possibility is that the Curator's IndexField inside the Piece Table is not included in the Main Query's recordset so that if you have added a new curator record it will not update the Piece Table automatically.

I managed to figure it out, and that was exactly what the problem was. Thanks so much for the reply. Learned a few things from that post...everything seems to be in order. Amazingingly enough!!



http://www.PCGuide.com/ubb/biggrin.gif http://www.PCGuide.com/ubb/biggrin.gif

[Paul Komski]

http://www.PCGuide.com/ubb/smile.gif Glad you got it sorted. Having automatically updatable queries are fine up to a point and for straightforward setups. The absence of the primary key field is just one of many reasons that will prevent such automation. You can be more in control (and thus more quickly isolate a problem) if you organise to directly update the field/control on FormA from FormB.

HeHe - I don't know about the "Amazingly Enough" bit. Databases are superbly powerful tools; and as long as constructed well and with good code they should (nearly) always work!! http://www.PCGuide.com/ubb/biggrin.gif


------------------
Take nice care of yourselves - Paul
"People in glasshouses ..... shouldn't undress during daylight!"

[neveryona]

hehe...the amazingly enough stuff...is that I got it to work. http://www.PCGuide.com/ubb/rolleyes.gif It's been a bit of a struggle getting these old circuit paths in my brain working in such a logical manner. But you know, I gotta say I like it, I find it to be very therapeutic on the mind. ANd thanks once again for your help. http://www.PCGuide.com/ubb/smile.gif