Refreshing Subform Data

[bizzy]

G'day,

Currently I have a button on my form that opens a second for to Add a New Temp Worker. This all works fine but when I close the form and go back to the entry form the subform hasn't been refreshed and so the new temp isn't in the list. I can't close the form that contains the subform as it will stuff up the data entry.

Can you please help?

Bizzy

 

[RuralGuy]

Use the acDialog parameter when you open the other form and issue a DoCmd.Requery right after the DoCmd.OpenForm line.

 

[bizzy]

Riiiight?

I'm not sure that I know how to do that?

Any chance of the code?

 

[RuralGuy]

No problem. Post all of the code from your button that opens the other form. Just do a Copy and Paste.

 

[bizzy]

Here you go

Private Sub cmdAddNewTemp_Click()
On Error GoTo Err_cmdAddNewTemp_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAddTemp(NewClientObservations)"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec

Exit_cmdAddTemp_Click:
Exit Sub
Exit_cmdAddNewTemp_Click:
Exit Sub

Err_cmdAddNewTemp_Click:
MsgBox Err.Description
Resume Exit_cmdAddNewTemp_Click

End Sub

 

[RuralGuy]

Here's a modified subroutine that should do as you desire:

Private Sub cmdAddNewTemp_Click()
On Error GoTo Err_cmdAddNewTemp_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAddTemp(NewClientObservations)"
[B]'DoCmd.OpenForm stDocName, , , stLinkCriteria
[COLOR=Red]'-- Since you never set the stLinkCriteria, it is not needed.[/COLOR]
DoCmd.OpenForm stDocName, , , , , acDialog
'[COLOR=Red]-- This code will not resume until you close the stDocName form[/COLOR]
DoCmd.Requery ' Will requery everything on this form
[COLOR=Red]'-- Why are you going to a new record here?[/COLOR][/B]
DoCmd.GoToRecord , , acNewRec

Exit_cmdAddTemp_Click:
   Exit Sub

Err_cmdAddNewTemp_Click:
   [B]MsgBox "Error No:    " & Err.Number & vbCr & _
          "Description: " & Err.Description[/B]
   Resume Exit_cmdAddNewTemp_Click
End Sub

 

[bizzy]

New Record

I go to a New Record because they use this button if the Temp worker they are trying to find isn't in the drop down list. So they open the form to add a new record (temp).

Is my code ok?

 

[RuralGuy]

I thought that was being done in frmAddTemp(NewClientObservations) not on this form.

Are you using a ComboBox to locate the Temp worker? If so, why not just go ahead and use the NotInList event of the cbo and be done with it?

It sounds like your code could benefit from a review.

 

[bizzy]

Ok then.....

Currently I have my form with Client Details on it. I then have a subform (in datasheet view). In the subform they select a temp worker. I need it so that if the Temp worker is NOT in the drop down list that they can add it and select them.

Is this possible? If So how?

Bizzy

P.S I am new to access and am still learning so yes my code probably does need an overhaul. But don't know anyone to do it.

 

[RuralGuy]

If you want, strip out any sensitive data, compact and repair the db Tools>Database Utilities, zip up what is left and post it to this forum. I will look at it as will others and possibly offer many suggestions.

 

[bizzy]

That's Awesome.

Only problem is I compacted and repaired it and it's still 21.9 meg? Is that due to forms? Any way to make it smaller?

 

[RuralGuy]

Holy Moly. Do you have any huge tables inside? Make a copy of the db (which you should have already done) and with the copy delete most of the records in the tables if they don't hurt the application. Do you know how to do a decompile? Here's a link of things that might make it smaller:
http://allenbrowne.com/ser-47.html

Allen describes several steps. Follow them carefully. Always working with a copy of the real mdb. Good luck.

Are you storing pictures in the db by chance?

 

[bizzy]

Here's the file

Thanks for having a look. Hope it makes sense.

My boss only needs it by the end of the week. Pfft.

Don't mind the Employees table and everything linked to that. That is something seperate.

Thanks

Bizzy

P.S I worked out why it was so big. It was the Company logo file.

 

[RuralGuy]

It will probably be tomorrow before I finish the review.

 

[bizzy]

Thanks

No worries. Look forward to it.

Cheers

Bizzy

 

[RuralGuy]

Bizzy,

Contact me when you get a chance.

 

[bizzy]

I'm here.........

Hey RuralGuy.

How can I help?

Bizzy

 

[RuralGuy]

Hi Bizzy,

Did you get the PM?