Requery after add

[Guest]

Hi,

I've been trying to figure out how to requery a subform after I have added a
record to the main form.

I have a combo box in the form header that looks up a record in the main
form and gives the related info in the subform. I have also added code to
enter the new value to the table if the value does not already exist in the
table as indicated on this website -
http://www.mvps.org/access/forms/frm0015.htm

The problem is that when a new value is added to the table it doesn't update
the form and subform properly. It automatically brings up the first record,
even though an new value has been added to the table. I've seen posts that
recommend adding some code to the after update event of the combo box - the
problem that I'm having with that is that I already have code in that event
and I don't know how to add this new code. Is there someplace else that I
should be entering the code to requery the combo box? If so, what code and
where should it be placed?

Thanks
Joey

 

[Guest]

If you are entering a new value into the combo there won't be any
information to update in the subform unless I have misunderstood the problem.

 

[Guest]

Having said that, you don't say what you want to do once you have entered
the new value. Do you want the subform to go to a new record?

 

[Guest]

The new value should have no records showing on the subform since it is new,
yet its showing the information for the first record in my table. I want it
to show the records related to that new record (which will be nothing, except
for the new name that I just entered, on the main form), rather than the
information related to the first record in my table - which is showing up on
the main form as well as the subform.

 

[Guest]

In the "afterupdate" event of the combo it might just be a simple case of

[forms]![form name]![subform name].requery

 

[BruceM]

The new value should have no records showing on the subform since it is
new,
yet its showing the information for the first record in my table. I want
it
to show the records related to that new record (which will be nothing,
except
for the new name that I just entered, on the main form), rather than the
information related to the first record in my table - which is showing up
on
the main form as well as the subform.

Are the tables related properly, and are the Link Master and Link Child
properties of the subform control properly established? If your main form's
table is tblMain with a primary key field MainID, then there needs to be a
corresponding field in the subform's table (tblSub).

tblMain
MainID (primary key, or PK)
Other fields

tblSub
SubID (PK)
MainID (foreign key, or FK)
Other fields

There is a one to many relationship between tblMain and tblSub, established
through the MainID fields in the two tables.

Now, in the main form's design view, click the edge of the subform control
(the "container" in which the subform is located). Click View > Properties,
and click the Data tab. The Link Master and Link Child properties should
both be MainID. If they are blank, click the three dots. The suggestions
will probably be the correct choice, provided the table relationships are
correct.

These steps should ensure that the subform records correspond to the correct
main form record.

 

[Guest]

BruceM - The tables are linked properly - it works when I look up a value
that is already on the list, I only have this problem when I try to add a
value.

scubadiver - the problem with that is that I already have code in the
afterupdate, I don't know where to put that code that you mentioned.

 

[Guest]

Here is the code that I have for this form, maybe it will help. I'm trying
to requery, but its not working, maybe I have it in the wrong place....

Private Sub Combo6_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo6], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


End Sub

Private Sub Combo6_NotInList(NewData As String, Response As Integer)
Dim strSQL As String

Dim i As Integer

Dim Msg As String

'Exit this sub if the combo box is cleared

If NewData = "" Then Exit Sub

Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr

Msg = Msg & "Do you want to add it?"

i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Client...")

If i = vbYes Then

strSQL = "Insert Into [Clients] ([ClientName]) " & "values ('" &
NewData & "');"

CurrentDb.Execute strSQL, dbFailOnError

Response = acDataErrAdded

Else

Response = acDataErrContinue

End If

[Forms]![Clients Form]![CallLog subform].Requery
End Sub

Private Sub Form_Current()
Me.Combo6.SetFocus
End Sub

Private Sub Form_Load()
If Not Me.NewRecord Then
RunCommand acCmdRecordsGoToNew
End If

End Sub

 

[BruceM]

You didn't specifically say that the Link Child and Link Master properties
of the subform control are properly established, but you did say that the
tables are properly related, so I'll assume the Link properties are OK too.
Have you tried requerying the main form?

 

[Guest]

That's what I'm trying ask about. Its not working.

 

[BruceM]

In your latest code you requeried the subform. I was wondering about the
effect of requerying the *main* form. Also, Yes or No: I have checked the
Link Master and Link Child properties of the subform control, and have found
that they are correct.

 

[Guest]

[Forms]![Clients Form]![CallLog subform].Requery

Clients Form is the main form and CallLog Subform is the subform. What am I
missing?

 

[BruceM]

You are requerying the subform only. You are not requerying the main form.
To requery the main form, replace the line of code with Me.Requery

[Forms]![Clients Form]![CallLog subform].Requery

Clients Form is the main form and CallLog Subform is the subform. What am
I
missing?

In your latest code you requeried the subform. I was wondering about the
effect of requerying the *main* form. Also, Yes or No: I have checked
the
Link Master and Link Child properties of the subform control, and have
found
that they are correct.

 

[Guest]

I replaced teh line of code and now I'm in an endless loop. It keeps
prompting me that the client is new and asks if I want to add the new client.
When I click on Yes, it brings me back to the same prompt, and I can't get
out of it.

 

[BruceM]

Review the question for which I requested a Yes or No response in the
previous message. It really matters, which is why I have asked about it
several times.
If the properties are not set correctly, setting them may eliminate the
problem. If they are set correctly, try Me.Refresh instead of Me.Requery.

 

[Guest]

The refresh also put me in an endless loop.

As far as the master and child fields, maybe I didn't phrase it properly
yesterday morning, but the Child and Master properties are set correctly. I
didn't realize that you were asking me to respond to the yes or no question,
it seemed as if you telling me that you checked the properties and they are
correct - although I had no idea how you could have checked my computer for
that. I would assume that it wouldn't work at all if they weren't related
properly, but as I've stated previously, it looks up the information for the
subform properly when I choose somebody who is already in table, I am ONLY
having this problem when I enter a new person into the list.