Running 2 Queries and Opening A Form

[k5kowboy]

OK..here is my dilema. I have a check box on my form that I am working with. I have written the code for it to perform the required function..the only problem is that it only does it the 3rd time I check the box. Here is what I need it to do...when I check the box to indicate a "yes", I need it to:

1. Move select data from "table 1" to "table 2" (done with an Append Query)
2. Delete that particular record in "table 1"(done with a Delete Query)
3. Open a new form for "table 2" and add more informationi.

here is the code I have plugged into the event procedure. Like I said...it works fine the 3rd time I check the box....just not the first two times I do it.

stDocName = "Update Inventory From Gen Info"
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
stDocName = "Delete From Main After Inventory Update"
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
DoCmd.OpenForm "Inventory"

Any ideas on what I need to do to make it work the first time I check the box instead of having to do check it multiple times.

 

[bradcccs]

Which event are you using?

 

[k5kowboy]

"On Click"

 

[bradcccs]

The on-click event fires every time the check box is clicked (in theory anyway)

Could you post the full code of the form.

Or try adding a Msgbox to the event and comment out the queries temporarily to see if it is actually firing each time - It could be the queries causing an issue.

Brad.

 

[k5kowboy]

Brad ~

I e-mailed the code to your e-mail address listed here. Thanks for your help. This is my first TRUE big project for someone and am really wanting to get it right the first time.

Thanks
Ross
k5kowboy@classicnet.net

 

[bradcccs]

Ross,

Sorry to repost, but I thought it best that your code be posted here.

A lot of people follow posts, and when code is posted privately, others are not able to see solutions / outcomes / arguments etc.

Code as from Ross:

  Private Sub Repo_d_Click()
    
    stDocName = "Update Inventory From Gen Info"
    DoCmd.OpenQuery stDocName, acViewNormal, acEdit
    stDocName = "Delete From Main After Inventory Update"
    DoCmd.OpenQuery stDocName, acViewNormal, acEdit
    DoCmd.OpenForm "Inventory"
      
    
End Sub

One thing I did forget to mention which is probably quite relevant is...the DoCmd.OpenForm works each time I click the box...it's just the queries that take 3 times for them to run.

You are correct, the OpenForm action occuring is very relevant.

The fact that the queries are not actioning every time is not due to the On_Click event.

I would be checking the relationship of the queries to the form state. ie: how does the query retrieve criteria from the form etc.

You could post the Query SQL or design grid image, and someone may be able to assist.

Brad.

 

[k5kowboy]

When I run each query individually under the Objects Query page, each one of them functions as they should - I.E., the Append Query updates the appropriate columns in the Inventory Table with the records that I have selected with a "YES" in the appropriate field and the Delete Query deletes that record from the General Information Table.

Here is the SQL from the Append Query:

INSERT INTO Inventory ( [Date Recieved], [Order Number], Color, [Year], Make, Model, [VIN First 11], [Vin Last 6], [License Plate], [License Plate State], [Bounty Amount], [Finance Company], [Debtor Last Name], [Debtor First Name], [Debtor Address], City, State, [Zip Code], [Last Update], Remarks, [Repo'd], [Type of Repo] )
SELECT [General Information Table].[Date Recieved], [General Information Table].[Order Number], [General Information Table].Color, [General Information Table].Year, [General Information Table].Make, [General Information Table].Model, [General Information Table].[VIN First 11], [General Information Table].[Vin Last 6], [General Information Table].[License Plate], [General Information Table].[License Plate State], [General Information Table].[Bounty Amount], [General Information Table].[Finance Company], [General Information Table].[Debtor Last Name], [General Information Table].[Debtor First Name], [General Information Table].[Debtor Address], [General Information Table].City, [General Information Table].State, [General Information Table].[Zip Code], [General Information Table].[Last Update], [General Information Table].Remarks, [General Information Table].[Repo'd], [General Information Table].[Type of Repo]
FROM [General Information Table]
WHERE ((([General Information Table].[Repo'd])=Yes));
Here is the SQL from the Delete Query:

DELETE [General Information Table].[Date Recieved], [General Information Table].[Order Number], [General Information Table].Color, [General Information Table].Year, [General Information Table].Make, [General Information Table].Model, [General Information Table].[VIN First 11], [General Information Table].[Vin Last 6], [General Information Table].[License Plate], [General Information Table].[License Plate State], [General Information Table].[Bounty Amount], [General Information Table].[Finance Company], [General Information Table].[Debtor Last Name], [General Information Table].[Debtor First Name], [General Information Table].[Debtor Address], [General Information Table].City, [General Information Table].State, [General Information Table].[Zip Code], [General Information Table].[Repo'd], [General Information Table].[Date Closed], [General Information Table].[Last Update], [General Information Table].Remarks, [General
Information Table].[Debtor Phone Number], [General Information Table].[Place of Employment], [General Information Table].[Employers Address], [General Information Table].[Employers City], [General Information Table].[Employers Phone], [General Information Table].[Type of Repo]
FROM [General Information Table]
WHERE ((([General Information Table].[Repo'd])=Yes));

 

[bradcccs]

Sorry, I didn't realise that the Check box is bound to the Repo'd state in the main table.

Given that your Sub is based on the "Repo_d_Click()" event, and your SQL uses a where statement based on "Repo'd" state :

The Click event occurs before the update event of the record.

Thus when the Append / Delete queries action, the record "Repo'd" state is currently "unticked" (False).

Thus the query runs but no records are transferred.

The open form then occurs.


Solution (Hopefully):
You will need to save the record prior to running queries.

This will save the state of Repo'd as true, and thus satisfy the where statement of your SQL

Alter your code to read:

Private Sub Repo_d_Click()
    
    DoCmd.RunCommand acCmdSaveRecord
    stDocName = "Update Inventory From Gen Info"
    DoCmd.OpenQuery stDocName, acViewNormal, acEdit
    stDocName = "Delete From Main After Inventory Update"
    DoCmd.OpenQuery stDocName, acViewNormal, acEdit
    DoCmd.OpenForm "Inventory"
      
    
End Sub

Note: I do not see this a particularly good way to have the user transferring records from one table to another. Unless you give the user a chance to abort from the transfer, I can see a few calls coming your way asking how to "get them back - cos I clicked it by accident"

Let me know how you get on

Brad.

 

[bradcccs]

As an aside, if you are keen to develop user apps, you will benefit greatly by doing some reading (search this forum) on naming conventions and normalisation.

(You are breaking just about every rule written )

Brad.

 

[k5kowboy]

Brad ~

Thanks for the help. Everything I have learned has been through trial and error. I didn't even realize there were normalisms for naming. You have been a great help and I have the problem fixed now - and I worked a way to prevent an "accidental" clicking of the checkbox.

Now, I have one more wall I've run up against that maybe you can help me out with. I have a couple of fields in my form (one for calculating the total number of days an item is in inventory and another for calculating amount owed for storage fees)

However, the calculated amounts do not show up in the table, I am assuming because the "Control Source" does not link it to the table, but rather is the logical expression to do the calculations. How do I get around that so the values will save to the table?

Thanks again for ALL the help.

 

[Tiro Cupidus]

Do you really need to store the calculation results in a table? I would personally prefer having a query display everything you need, therefore avoiding storing extra data.

 

[k5kowboy]

This particular time, I do need the calculation results stored because at the end of the year, I have to figure out total storage days for each company I am involved with. Legalities are such a pain sometimes