COMBO BOX REQUERY

[Glint]

Hi Guys,

Please help me out of this problem.

Because I don't want to use the NotInTheList Event for my combo boxes, I
have this code in the Close Event of my Membership form:

If IsLoaded("Donations") Then 'Forms!Donations!Donor.Requery

I simply hide the Donations form while a user updates the Membership form.
But I have just found that the code appears not work (I do not find my new
member until the next time the Donations form is opened) after I changed the
record source of the Donors combo to a union query:

SELECT Membership.SerialNumber, [Surname] & ", " & [Firstnames] AS Name,
Membership.Zone
FROM Membership
ORDER BY [Surname] & ", " & [Firstnames]
UNION ALL SELECT 10000000+Friends.FriendID, [Surname] & ", " & [OtherNames]
AS Name, Friends.Zone
FROM Friends
UNION ALL SELECT 20000000+Program.ProgramID, Program.Program, Program.Zone
FROM Program
ORDER BY Name;

I need to display all possible sources of donations in the combo, hence the
need for the union query. The code used to work when the record source of the
Donor combo was just the Membership table only.

Interestingly, the code works when a record is deleted from any of the
tables of the union query, but refuses to acknowledge a new record.

Please help me out.

 

[Glint]

Yes, the tick was a typo. It was not in the code.
When I requery the entire form as

If Isloaded("Donations") Then Forms!Donations.Requery

I get the error message

"Forms 'Donaions' isn't open"
--
Glint

Is the tick a typo?
If IsLoaded("Donations") Then ' Forms!Donations!Donor.Requery

What is the Donor control?
Why not Requery the entire form?
Why do you not want to use the NotInList event?

Hi Guys,

Please help me out of this problem.

Because I don't want to use the NotInTheList Event for my combo boxes, I
have this code in the Close Event of my Membership form:

If IsLoaded("Donations") Then 'Forms!Donations!Donor.Requery

I simply hide the Donations form while a user updates the Membership form.
But I have just found that the code appears not work (I do not find my new
member until the next time the Donations form is opened) after I changed the
record source of the Donors combo to a union query:

SELECT Membership.SerialNumber, [Surname] & ", " & [Firstnames] AS Name,
Membership.Zone
FROM Membership
ORDER BY [Surname] & ", " & [Firstnames]
UNION ALL SELECT 10000000+Friends.FriendID, [Surname] & ", " & [OtherNames]
AS Name, Friends.Zone
FROM Friends
UNION ALL SELECT 20000000+Program.ProgramID, Program.Program, Program.Zone
FROM Program
ORDER BY Name;

I need to display all possible sources of donations in the combo, hence the
need for the union query. The code used to work when the record source of the
Donor combo was just the Membership table only.

Interestingly, the code works when a record is deleted from any of the
tables of the union query, but refuses to acknowledge a new record.

Please help me out.

 

[Glint]

Thanks, Rurlguy.

As I mentioned earlier, the tick was a typo; it was not in the code.
The Donor is a combo box that has its rrecord source as:
SELECT vDonorNames.SerialNumber, vDonorNames.Name FROM vDonorNames;
vDonorNames is a union query like this:
SELECT Membership.SerialNumber, [Surname] & ", " & [Firstnames] AS
Name, Membership.Zone
FROM Membership
ORDER BY [Surname] & ", " & [Firstnames]
UNION ALL SELECT 10000000+Friends.FriendID, [Surname] & ", " & [OtherNames]
AS Name, Friends.Zone
FROM Friends
UNION ALL SELECT 20000000+Program.ProgramID, Program.Program, Program.Zone
FROM Program
ORDER BY Name;

Now, the code below works for Donations but does not work for the
SatsangClass form:
Dim rstDonations As DAO.Recordset, rstSatsangMember As
DAO.Recordset, rstArahata As DAO.Recordset
Dim rstPledge As DAO.Recordset, rstSpeaker As DAO.Recordset,
rstOfficiator As DAO.Recordset
If IsLoaded("Donations") Then
Set rstDonations = CurrentDb.OpenRecordset("vDonorNames")
rstDonations.Requery
End If
If IsLoaded("SatsangClass") Then
Set rstArahata = CurrentDb.OpenRecordset("vNames")
Set rstSatsangMember = CurrentDb.OpenRecordset("vDonorNames")
rstArahata.Requery
Forms!SatsangClass!Arahata.Requery
rstSatsangMember.Requery
Forms!SatsangClass!SatsangMembers!Member.Requery
End If

So I am totally confused, because it appears to work sometimes but not
everytime. vNames is a simple query based on Membership table. I have tried:
Forms!Donations!Donor.Requery
Forms!SatsangClass!Arahata.Requery
without success, although it works for a deletion, meaning there is some
requery process.

Can you recoomend something I can rely on? I cannot use NotInList Event
because the data needed for a record will not be captured through the combo.

--
Glint

Is the tick a typo?
If IsLoaded("Donations") Then ' Forms!Donations!Donor.Requery

What is the Donor control?
Why not Requery the entire form?
Why do you not want to use the NotInList event?

Hi Guys,

Please help me out of this problem.

Because I don't want to use the NotInTheList Event for my combo boxes, I
have this code in the Close Event of my Membership form:

If IsLoaded("Donations") Then 'Forms!Donations!Donor.Requery

I simply hide the Donations form while a user updates the Membership form.
But I have just found that the code appears not work (I do not find my new
member until the next time the Donations form is opened) after I changed the
record source of the Donors combo to a union query:

SELECT Membership.SerialNumber, [Surname] & ", " & [Firstnames] AS Name,
Membership.Zone
FROM Membership
ORDER BY [Surname] & ", " & [Firstnames]
UNION ALL SELECT 10000000+Friends.FriendID, [Surname] & ", " & [OtherNames]
AS Name, Friends.Zone
FROM Friends
UNION ALL SELECT 20000000+Program.ProgramID, Program.Program, Program.Zone
FROM Program
ORDER BY Name;

I need to display all possible sources of donations in the combo, hence the
need for the union query. The code used to work when the record source of the
Donor combo was just the Membership table only.

Interestingly, the code works when a record is deleted from any of the
tables of the union query, but refuses to acknowledge a new record.

Please help me out.

 

[Glint]

Thanks again, my man.
I resorted to the combo requery in addition to the recordset requery out of
frustration when the recordset requery alone did not work for the other
SatsangClass form. The forms are loaded alright, just hidden temporarily
while the Membership form is being updated.
--
Glint

Thanks, Rurlguy.

As I mentioned earlier, the tick was a typo; it was not in the code.
The Donor is a combo box that has its rrecord source as:
SELECT vDonorNames.SerialNumber, vDonorNames.Name FROM vDonorNames;
vDonorNames is a union query like this:
SELECT Membership.SerialNumber, [Surname] & ", " & [Firstnames] AS
Name, Membership.Zone
FROM Membership
ORDER BY [Surname] & ", " & [Firstnames]
UNION ALL SELECT 10000000+Friends.FriendID, [Surname] & ", " & [OtherNames]
AS Name, Friends.Zone
FROM Friends
UNION ALL SELECT 20000000+Program.ProgramID, Program.Program, Program.Zone
FROM Program
ORDER BY Name;

Now, the code below works for Donations but does not work for the
SatsangClass form:
Dim rstDonations As DAO.Recordset, rstSatsangMember As
DAO.Recordset, rstArahata As DAO.Recordset
Dim rstPledge As DAO.Recordset, rstSpeaker As DAO.Recordset,
rstOfficiator As DAO.Recordset
If IsLoaded("Donations") Then
Set rstDonations = CurrentDb.OpenRecordset("vDonorNames")
rstDonations.Requery
End If
If IsLoaded("SatsangClass") Then
Set rstArahata = CurrentDb.OpenRecordset("vNames")
Set rstSatsangMember = CurrentDb.OpenRecordset("vDonorNames")
rstArahata.Requery
Forms!SatsangClass!Arahata.Requery
rstSatsangMember.Requery
Forms!SatsangClass!SatsangMembers!Member.Requery
End If

So I am totally confused, because it appears to work sometimes but not
everytime. vNames is a simple query based on Membership table. I have tried:
Forms!Donations!Donor.Requery
Forms!SatsangClass!Arahata.Requery
without success, although it works for a deletion, meaning there is some
requery process.

Can you recoomend something I can rely on? I cannot use NotInList Event
because the data needed for a record will not be captured through the combo.

Is the tick a typo?
If IsLoaded("Donations") Then ' Forms!Donations!Donor.Requery

[quoted text clipped - 36 lines]

Please help me out.

 

[Glint]

Thanks again my man.

I think you may have a point there about corruption. I am shuttling the
application between my laptop and desktop (Access 2003 and 2002
respectively), and even though I opt to compact on close, some corruption may
have developed over time.

Meanwhile, I have changed the approach: I open the forms to be updated in
dialog mode instead of simply hiding the form to be requeried. This way,
these is less coding; just the 'inconvenience' of having two forms on display.

I will still like to know why the requery code sometimes failed; I have a
hunch I may need the code sooner or later.
--
Glint

What version of Access are you using? How about importing everything into a
new empty mdb and then do a Compact and Repair to eliminate corruption as a
potential problem.

Thanks again, my man.
I resorted to the combo requery in addition to the recordset requery out of
frustration when the recordset requery alone did not work for the other
SatsangClass form. The forms are loaded alright, just hidden temporarily
while the Membership form is being updated.

On this one I'm not sure what you are doing. It looks like you "Set" a
RecordSet and then immediately requery it which is absolutely unnecessary.

[quoted text clipped - 50 lines]

Please help me out.