Have to enter Value Twice to Run Append/Delete Queries

[rhounsome]

I have created an append query and delete query which upon a click of a button runs a macro which requests a record number to be entered. At present the append query requests the record number to append a record to a USED TABLE. The delete query asks for the record number to be entered again so it can delete it from the CURRENT TABLE.

Do I really need it to ask twice or will the append query retain the selected record for it to be deleted by the delete query.

Also can anyone show the VBA Code to do this because when the form is replicated the macros suddenly stop working

 

[Fornatian]

You are better using unbound text/combo controls as criteria as they can be re-referenced.

So in your query where you currently have

[Criteria Question?]

you can replace that with

Forms!MyForm!FormControl

which takes the value of an unbound control on the form MyForm and uses that as criteria instead.

Doing this in both queries will prevent any duplicate questions being asked as the criteria will be there on both requests.

Ian

 

[rhounsome]

Sorry this has taken sooooooooo long to respond back to.

You say use an unbound text field as the source as these can be linked to any part of a database.

The query I am trying to run is based on the form the button that the append / delete queries operate from. Could I use that instead of creating a new form just for this function?

Also I assume that if I click this button the focus is taken away from this field I am wanting to append/delete and hence is entered into the database?

 

[rhounsome]

Forgot this might help you

Field name I am

3_Letter_ID (Function is drop down list)

At present the Conirm Button runs a macro which consists of 2 queries

Append 3_Letter_ID and Delete 3_Letter_ID

It wasn't so much of an issue until we tried replicating the database and suddenly found Macros cease to function. Hence the long delay in getting back

 

[Jack Cowley]

On the form with the command button create a combo box that looks up the record you want to append. Do another combo for the record to delete. (Use one combo box if it is the same ID). In the Append query use code like this in the Criteria line of fields ID:

[Forms]![NameOfFormWithComboBox]![ComboBoxName]

Do this for the other query.

In the On Click event of your command button select [Event Procedure] and then add code similar to this:

DoCmd.OpenQuery "AppendQueryName"
DoCmd.OpenQuery "DeleteQueryName"

Convert your macros to code using the Convert menu in Access and you will be happier in the long run....

 

[Fornatian]

Nice One Jack, I was about to say that, saved me a few brain cells burning out

 

[rhounsome]

Thanks for the answers and it now works based on everyones answers and some looking around, I managed to create some code which is based on the information from the form you are looking at.

It is inserted on Click

Dim SQL As String

SQL = "INSERT INTO [3 Letter Lookup Used DO NOT AMEND] ( 3LETREF )SELECT [3 Letter Lookup DO NOT AMEND].[3LETREF]FROM [3 Letter Lookup DO NOT AMEND]"
SQL = SQL & "WHERE ((([3 Letter Lookup DO NOT AMEND].[3LETREF])=[Forms]![Company]![3 Letter Reference]));"
On Error Resume Next

DoCmd.RunSQL SQL
Dim SQL1 As String

SQL1 = "Delete [3 Letter Lookup DO NOT AMEND].[3LETREF]FROM [3 Letter Lookup DO NOT AMEND]"
SQL1 = SQL1 & "WHERE ((([3 Letter Lookup DO NOT AMEND].[3LETREF])=[Forms]![Company]![3 Letter Reference]));"
On Error Resume Next

DoCmd.RunSQL SQL1

As I am new to this and making it up as I go. This site of unlimited knowledge is great, far better than books keep those Q and A's coming

 

[Fornatian]

There is no benefit to using an SQL statement in code over a predefined query in your case as you are not building the query dynamically. Queries are 'pre-optimised', so run faster.

If you are going to leave it how it is, you can recycle the SQL string variable and use it for both statements by re-assignning t after running the first query, thus saving the memory space used by SQL1.

Personally I'd use queries as Jack explained.

HTH

Ian

 

[rhounsome]

Thanks for the info.

As mentioned being a novice to all this, I guess there is a lot to learn.

Still on the append/delete issue. Notification can be turned off on the toolbars on the top, so it never asks but if the user is not aware of this is there anyway to turn it off when the database is first opened by using a bit of code.

 

[simongallop]

Wouldn't suggest turning it off when you open the db. Would suggest turning notification off and then on purely for the append / delete code.

Insert this line at the beginning of the OnClick event:

Docmd.SetWarnings False

and this line at the end:

DoCmd.SetWarnings True

HTH

 

[rhounsome]

I know its going to sound a bit thick but you are stating to just turn off the notification for a period of time to get through an issue.

Is this because notification relates to every on screen pop up message?

 

[David R]

Correct. If you turn off warnings in general then NO error messages/confirmations will be displayed for your entire use of the database and that can lead to Very Bad Things[tm].

David R