opening a form with Append queries 2

[techkenny1]

Hi all,

I use an unbound combo box to select forms. On one form I need to activate 2 append queries before the form can open.
I could use a command button to open the form and put the append queries before the open form command, but I want to be able to use the unbound combo box to select and open forms.The system works ok for all the forms, except this one as it requires the append queries to open for the form to see the data.
I have tried putting the 'open queries' on several of the event Procedures on the form, but can not get it to work
Any help appreciated.
kp

 

[dhookom]

Why not run the append queries and then open the form. It seems you are attempting to put the cart in front of the horse.

Duane
Hook'D on Access
MS Access MVP

 

[techkenny1]

Hi Dhookom,
You are correct. But when using a combo box to open forms was my dilema. The only solution I can see is to select the form via the combo box, at the same time runing the append queires and then run a requery.

Would this be a way to do it? To run the append queries I would need a command button.. but wanted to do this in one action

kp

 

[dhookom]

An unbound combo box doesn't do anything unless you tell it to. We haven't seen any code.

I'm not sure anyone but you understands the append queries or forms or anything else.

Duane
Hook'D on Access
MS Access MVP

 

[techkenny1]

Hi DHookham,
Thanks for your reply. Here is the code for the combo box.
The combo box uses this code to select forms. I have just posted a portion of it.
The Row Source is:
"SELECT MSysObjects.Type, MSysObjects.Name FROM MSysObjects WHERE (((MSysObjects.Type)=-32768) AND ((MSysObjects.Name)="Day Running")) OR (((MSysObjects.Type)=-32768) AND ((MSysObjects.Name)="Debtors"))"

On the AfterUpdate event I have this code;
Private Sub ComboBox Forms_AfterUpdate()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = Me.ComboBoxForms
DoCmd.Openform stDocName, , , stLinkCriteria
End Sub

The append queries are
"AccountsStoreB1" and "AccountsStoreB1"
Both of these update a table to hold data from 2 other tables, which are tblDebtors and tbl sales. A delete query is run when the form closes

The form "sales" has this code;
SELECT AccountsHold.Invoices, AccountsHold.BrokerStaffID, AccountsHold.InvoiceNumber, AccountsHold.DateRaised, AccountsHold.DateRcvd, AccountsHold.Companyname, AccountsHold.InvoiceTotal, AccountsHold.BalDue, AccountsHold.AmmtRcvd, AccountsHold.CreditT, AccountsHold.ChqNo, AccountsHold.PD
FROM AccountsHold;

So basically what I want to do is to be able to run the 2 append queries, when the form is selected.

What I have tried is that in the form I want to open I have put this code on the open event;
DoCmd.OpenQuery "AccountsStoreB1"
DoCmd.OpenQuery "AccountsStoreB2"
Me.Requery

This works ok. Would this be the way to do it.

kp

 

[TheAceMan1]

How are ya techkenny1 . . .

[blue]dhookom[/blue] is right in his post [blue]11 Mar 10 0:43[/blue] ... just run the append queries first for that one form. Here's example code for your [blue]AfterUpdate[/blue] event:

[blue]   Dim db As DAO.Database, frmNam As String
   
   Set db = CurrentDb
   frmNam = Me.ComboBoxForms
   
   If frmNam = "Day Running" Then
      db.Execute "AppendQueryName1", dbFailOnError
      db.Execute "AppendQueryName2", dbFailOnError
   End If
   
   DoCmd.OpenForm frmNam
   
   Set db = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

I think TheAceMan1 has the right code. The only thing I would change is to not depend on the msysObjects table. Take the two minutes to create a small table that has the form names and possibly a field that identifies if the append queries should be run or not.

Duane
Hook'D on Access
MS Access MVP

 

[techkenny1]

Thanks guys,
That seems to work great.
kp

 

[TheAceMan1]

How are ya [blue]dhookom[/blue] . . .

Its an honor working with you ... Sir! I've seen your work across many websites.

Just my opinion, but I think [blue]MSysObjects[/blue] is great ... [purple]espcially since it automatically takes care of form deletions & additions[/purple] (got to write code to take care of this using a seperate table) as we move forward in our form designs. However ... [blue]techkenny1[/blue] is [blue]explicit![/blue] in looking for two forms via the where clause. Namely [blue]Day Running[/blue] & [blue]Debtors[/blue]. As such ... I query why he didn't simply use a value list! ... [blue]Huh![/blue]

We'll see what happens on his return ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

Thanks TheAceMan1.
I prefer to give my tables, forms, queries, reports, etc names that are not for human consumption like frmDebtors, and frmDayRunning. Managing records in a user tables is what we do.

Since techkenny1 was using a table rather than a value list suggests he might want to update the code or whatever. Or, maybe he just didn't think of a value list.

A small table is so much more flexible and user friendly and easier to maintain.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

[blue]dhookom[/blue] . . .

Roger That! ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[techkenny1]

Hi guys,
Just returned to this post. I have created a table to hold the forms.
I am trying to keep work on this crap database at a level where it does not take up so much of my time, so hence taking shortcuts to get the work done. Probably not the best way, but at present the quickest.
Do appreciate the help u guys give.
kp