Table Link from SQL

[RWG]

I have a "form" with a command button that deletes a table and relinks that
table from SQL.(Done to change from one table to a different table but has
the same format, just a different set of records) Reads as follows

Private Sub cmdAttachTable_Click()

If MsgBos ("Attach table " & Forms!frmAttachTable!Attach & "?") Then

DoCmd.DeleteObject acTable, "Campaign"
Docmd.TransferDatabase acLink, "ODBC", ............
DoCmd.Close

End If
End Sub

Now sometimes (too often for me) I lose the link to the table and I have to
go in and manually link back to the table and rename it. What I would like
to do is to put a EXISTS and/or IF ELSE statement like follows

Private Sub cmdAttachTable_Click()

If MsgBos ("Attach table " & Forms!frmAttachTable!Attach & "?") Then

If table.EXISTS ("Campaign") = True THEN

DoCmd.DeleteObject acTable, "Campaign"
Docmd.TransferDatabase acLink, "ODBC", ............
DoCmd.Close

Else

Docmd.TransferDatabase acLink, "ODBC", ............
DoCmd.Close

End If

End If
End Sub

For some reason I get a error message

Run-time error '424':
Object required

Any help or suggestions?

 

[Wayne Morgan]

If MsgBos ("Attach table " & Forms!frmAttachTable!Attach & "?") Then

You're not specifying a value for the Return Value of the MsgBox to compare
against to get a true or false statement. The possible return values are
1-7, all of which would be treated as True (i.e. non of them are 0). Also,
unless it is a language difference, MsgBox is misspelled.

Example:
If MsgBox ("Attach table " & Forms!frmAttachTable!Attach & "?") = vbOk Then


To see if a table exists for your If statement in the second example, you
either need to loop through all of the TableDefs, comparing their name with
the name of the table you're looking for or just trap the error.

'If the table doesn't exist, ignore the error returned by trying to delete
it
On Error Resume Next

DoCmd.DeleteObject acTable, "Campaign"

Err.Clear
'resume your normal error handler here, either On Error GoTo 0
'or On Error GoTo LabelName
'Another option would be to just trap the error in your current error
handler
'and use a Resume Next statement. See example below.

Docmd.TransferDatabase acLink, "ODBC", ............
DoCmd.Close

Example:
CurrentErrorHandler:
If Err.Number = 424 Then Resume Next
'The rest of the current error handler here.

If the error is happening on a different line, post which line the error is
occurring on along with the full code for that line.

 

[RWG]

Thanks Wayne

The MsgBos was a typo, sorry. Tried your first suggestion using the On
Error Resume Next and the Err.Clear and it worked great. THANKS.

 

[RWG]

Thanks again Wayne

My next thing is I have a form that pulls information from table "Campaign"
as well as another table "Sales" based on a record id in the "Sales" table.
Would like the same action to happen as a background action prior to opening
the form for editing. What would the best way be? There is already a query
that has all the relevant info to make the selection from the passthrough
query used in the form mentioned in the previous action.

 

[Wayne Morgan]

It would depend on what you're wanting to happen. You could try the form's
Open event, it occurs before the form gets its recordset and can be canceled
so that you don't continue opening the form if what you were checking on
doesn't meet your criteria.

 

[RWG]

What I would like to happen is before the form opens, but after I type in the
value that this new form asks for, for the Campaign (has customer data) table
to be deleted (that part is easy), then create a link to the proper Campaign
table. Basically I want to happen just like the other but without having to
manually select the table to link as Campaign. The sales table has the
criteria I need to be able to select the path, I just can't figure out how to
use query criteria to make the selection instead of clicking on a form
manually.

 

[Wayne Morgan]

The sales table has the

criteria I need to be able to select the path, I just can't figure out how
to
use query criteria to make the selection instead of clicking on a form
manually.

I'm lost here. If the sales table is available, you can get a value from it
using the DLookup function in VBA. You would be able to get the value from a
field where the Sales ID matches the Sales ID you're currently using. Is
that what you're after? I don't understand the part about "... use query
criteria .... instead of clicking on a form manually." What are you clicking
on a form manually?

 

[RWG]

At the beginning of this thread, I asked for help on a form I use to select a
table link that first deletes the link to the old "Campaign" table (I needed
help with the just in case the link was not there) and then links to another
table and gives it the alias "Campaign". This table contains information
about customers and I have several different tables for different sets of
customers. All these tables are identical in format and I have a form
process to apply the payments. I process several dozen payments daily and it
is not time efficient to stop the flow to open the form that changes the
"Campaign" table and then go back to processing the payments. The process
adds to a payment table, edits fields in both the "Campaign" table and the
"Sales" table, so using an Union query for the "Campaign" table doesn't work
because of the inability to do updates or edits in Union results. When the
payment process starts, I would like to have the system to change the
"Campaign" table to reflect the change in which "Campagin" it is getting the
customer data from. The "Sales" table has a "campaignid" field that allows
me to know which "Campaign" I need to pull the customer data from. So,
instead of having the previous "Campaign" selection form being opened to
change the "Campaign", I would like the payment process to do it
automatically.

 

[Wayne Morgan]

Ok, I think I know where you're headed. Actually, a table redesign would
probably be best. Make one campaign table with an additional field to
indicate which campaign then filter on this field as needed or link the two
tables on this field. There would now be no need to swap out this table.

As far as the payment process doing it automatically, you would need code in
the AfterUpdate event of the control that holds the data to tell you which
table you need and in the form's Current event that does the same thing as
your other form does. To simplify things, you should probably put the
swapping routine in a module and call the module from the other form and
these two events, passing the table name to the routine.

The reason for the two events listed above is - 1) the AfterUpdate event
would be used if you changed the value, 2) the form's Current event would be
used as you move from record to record (and when the first record opens
after opening the form).

 

[RWG]

Having one "Campaign" table isn't an option for reasons to long to go into
here (proprietory system).

What The payment process does right now, and only after I have swapped into
the correct "Campaign" table, I click a button on the "Switchboard" in access
and a dialog box will pop up asking for the "Sales" id. I type in the sales
id number and up pops the payment form showing the customer information. If
the customer information looks the same as the return receipt, then I push a
button on the payment form, pops up a dialog box asking for the amount, thent
starts the append to the "Payment" table, update the "Sales" table, and
update the "Customer" table. Then the form closes and then it starts the
process over again. At which point in this process would I put in the
swapping routine and how do I code it to select the appropriate table, is it
query based, form based or how would I compare it to the "campaignid" field
to make the swap?

 

[Wayne Morgan]

You've previously stated that "The sales table has the criteria I need to be
able to select the path..." Is this table open in a form? If so, place a
button on the form and use the button's Click event or, even more
automatically, use the form's Current event to get this value as you move to
each record. The easiest way to get this value is to place a textbox on the
form and bind it to this field. You can set the Visible property of the
textbox to No if you don't want it to show on the form. You could then just
get the value of this textbox. As far as swapping the tables, the code would
then be the same as the code in the form you're saying you don't want to
have to open that already has this ability. This could also probably be done
in the Open event of the form that pops-up and asks you for the Sales ID.

You say that you click on a button to open the dialog box that asks for the
Sales ID, is this an Input Box or a form? What happens before you press this
button? Is the Switchboard the built-in Access made switchboard or a form
you've designed yourself?

Another possible option is to leave all of these tables linked and just
change the Record Source of the form to the correct table instead of
deleting and relinking the tables.

 

[RWG]

Thank you, it worked. Used a field in the Payment table to swap the table.
Now though the table is swapped, the underlying query is not showing the
customer information in the "Payment" form. Is there a way to force the
query to rerun itself to update to the new "Campaign" table. The underlying
query for the form has tables "Campaign" and "Sales" and "CampaignLocation"
with left joins from "Sales" to "Campaign" on [tel] and "CampaignLocation" on
[campaignid].

The Switchboard is the access built-in switchboard and the button points to
a Macro that opens the "Payment" form and has a WHERE condition of
[recordid]=[Enter File Number] - the dialog box that pops up.

 

[Wayne Morgan]

After swapping the tables, tell the form to Requery.

Me.Requery
or
Forms.MyForm.Requery

 

[RWG]

Tried and still no customer data if the "Campaign" table was the wrong one
before the swap. The "requery" action basically just reran the same thing
over again based on the same result set. So what I did was create a new form
and procedure to make the swap before doing the "Payment" form and it worked.

Thanks again for your help and ideas. It really helped and I learned some
stuff as well.

After swapping the tables, tell the form to Requery.

Me.Requery
or
Forms.MyForm.Requery

--
Wayne Morgan
MS Access MVP

Thank you, it worked. Used a field in the Payment table to swap the
table.
Now though the table is swapped, the underlying query is not showing the
customer information in the "Payment" form. Is there a way to force the
query to rerun itself to update to the new "Campaign" table. The
underlying
query for the form has tables "Campaign" and "Sales" and
"CampaignLocation"
with left joins from "Sales" to "Campaign" on [tel] and "CampaignLocation"
on
[campaignid].

The Switchboard is the access built-in switchboard and the button points
to
a Macro that opens the "Payment" form and has a WHERE condition of
[recordid]=[Enter File Number] - the dialog box that pops up.