Form not updating last record on datasheet (1 Viewer)

vent

Registered User.
Local time
Yesterday, 21:21
Joined
May 5, 2017
Messages
160
Hi all

I have a split form where the top half displays some agency information and the bottom half is a datasheet that contains records with more details. I have a form button for users to add new records and when I went to fill one out, I hit save, then close on the form but whenever I try to type in the agency name in the search bar, it doesn't show up on the datasheet. However when I look on the main table, I see the record is there. For some reason it's not showing up in the datasheet. Even though the split form's record source is a query that uses this main table. Thoughts?

Any feedback helps. Thank you.
 

Attachments

  • playco.PNG
    playco.PNG
    43.9 KB · Views: 212
  • maintable .PNG
    maintable .PNG
    1.9 KB · Views: 208
  • addnew.PNG
    addnew.PNG
    38.9 KB · Views: 210

Ranman256

Well-known member
Local time
Yesterday, 21:21
Joined
Apr 9, 2015
Messages
4,339
you should use the refresh button (on the toolbar), to update all screen records.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:21
Joined
May 7, 2009
Messages
19,169
Requery your form to show latest records. Your data is not updated automatically because the add new record is on diff form.
 

vent

Registered User.
Local time
Yesterday, 21:21
Joined
May 5, 2017
Messages
160
Requery your form to show latest records. Your data is not updated automatically because the add new record is on diff form.

You mean like on the add new record form?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:21
Joined
May 7, 2009
Messages
19,169
Yes do it there after adding the record
 

vent

Registered User.
Local time
Yesterday, 21:21
Joined
May 5, 2017
Messages
160
Yes do it there after adding the record

So after I hit save putting something like:

Code:
Me.Requery

In the On_Click property of the close button?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:21
Joined
May 7, 2009
Messages
19,169
use:

[Forms]![yourSplitFormName].Requery
 

vent

Registered User.
Local time
Yesterday, 21:21
Joined
May 5, 2017
Messages
160
use:

[Forms]![yourSplitFormName].Requery

I got an error that said:

Run-time error '2478':

Microsoft Access doesn't allow you to use this method in the current view


Code:
Private Sub cmdClose_Click()
[B][Forms]![frmAgencyLookup].Requery[/B][B][I]// this part is highlighted[/I][/B]
DoCmd.Close
End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:21
Joined
May 7, 2009
Messages
19,169
How did you setup frmAgencyLookup? Is this the splitform?
 

vent

Registered User.
Local time
Yesterday, 21:21
Joined
May 5, 2017
Messages
160
How did you setup frmAgencyLookup? Is this the splitform?

As a datasheet. It's record source is a query. The form frmAddAgency's record source is the main table itself
 

Attachments

  • playco.PNG
    playco.PNG
    43.9 KB · Views: 180

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:21
Joined
May 7, 2009
Messages
19,169
this is the Splitform right.

you might try to get the recordsource
of the splitform and put it back again

Dim strRecordSource As String
strRecordSource = [Forms]![yourSplitFormName].RecordSource
[Forms]![yourSplitFormName].RecordSource=strRecordSource
 

vent

Registered User.
Local time
Yesterday, 21:21
Joined
May 5, 2017
Messages
160
this is the Splitform right.

you might try to get the recordsource
of the splitform and put it back again

Dim strRecordSource As String
strRecordSource = [Forms]![yourSplitFormName].RecordSource
[Forms]![yourSplitFormName].RecordSource=strRecordSource

Sorry you mean this bit of code should be in the frmAddAgency form? (See picture)
 

Attachments

  • addnew.PNG
    addnew.PNG
    38.9 KB · Views: 172

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:21
Joined
May 7, 2009
Messages
19,169
You have save button there. After all the code in that button requery the splitform.
 

vent

Registered User.
Local time
Yesterday, 21:21
Joined
May 5, 2017
Messages
160
You have save button there. After all the code in that button requery the splitform.

I added this refresh button on the split form (frmAgencyLookup) and the on click property is [Event Procedure] and the code here is:
Me.Requery


The close button on the add new agency form (frmAddAgency) has an On Click property set to [Event Procedure] and the code here is:
Code:
Private Sub cmdClose_Click()
Dim strRecordSource As String
strRecordSource = [Forms]![frmAgencyLookup].RecordSource
[Forms]![frmAgencyLookup].RecordSource = strRecordSource
DoCmd.Close
End Sub

When I added a new record, the form closed and I didn't get any errors, and the record appeared on the table but still nothing showed up in the split form.
 

JHB

Have been here a while
Local time
Today, 02:21
Joined
Jun 17, 2012
Messages
7,732
Check if the form's property "Data Entry" is set to "No".
If you close the form and open it again, then all records in the table should show up in the form, if not, check if you've set any filter.
Else post your database with some sample data.
 

vent

Registered User.
Local time
Yesterday, 21:21
Joined
May 5, 2017
Messages
160
Check if the form's property "Data Entry" is set to "No".
If you close the form and open it again, then all records in the table should show up in the form, if not, check if you've set any filter.
Else post your database with some sample data.

I'm trying to post on here but it's not letting me. Something about proxy connection, I tried following the instructions but still no luck

The SQL for the query the datasheet and form is built on is this:
Code:
SELECT tblAgencyInformation.Agency, tblAgencyInformation.Subsidary, tblAgencyInformation.ProgramCodeID, tblAgencyInformation.ProgramNotes, tblAgencyInformation.PlacementType, tblAgencyInformation.Address, tblAgencyInformation.City, tblAgencyInformation.Prov, tblAgencyInformation.PostalCode, tblAgencyInformation.FirstName, tblAgencyInformation.LastName, tblProgramCodes.ProgramCode, tblProgramCodes.ProgramName
FROM tblProgramCodes INNER JOIN tblAgencyInformation ON tblProgramCodes.ProgramCodeID = tblAgencyInformation.ProgramCodeID
WHERE (((tblAgencyInformation.Agency) Like "*" & [Forms]![frmAgencyLookup]![SrchText] & "*"));

Below is also a screenshot of the query in design view. Also I attached a screenshot of the add new record form. Now the only way this works is if there's a value entered in the ProgramCodeID field as seen below. If this field is left blank I get this error:


The Microsoft Access database engine cannot find a record in the table 'tblProgramCodes' with key matching field(s) 'ProgramCodeID'


Once there's a value in there, the record appears on the datasheet however if there's no value, I get this error. Now I tried making the default value "*NOCODE" as that's what's normally entered if there's no program code, however, this doesn't seem to work
 

Attachments

  • query.PNG
    query.PNG
    22.3 KB · Views: 119
  • newrecord2.PNG
    newrecord2.PNG
    41.6 KB · Views: 108
Last edited:

vent

Registered User.
Local time
Yesterday, 21:21
Joined
May 5, 2017
Messages
160
Never mind guys, I got it working. THank you!
 

Users who are viewing this thread

Top Bottom