Replies

The symptoms described in this KB article are almost exactly what I experience, except there is no shift-key being pressed.
I’m going to try-out the code anyway (detecting shift-key), and see what happens.

I used an error handler, and absolutely no error occurs. Everything just stops with the newly opened workbook containing the text file data.

Sub GetNewData()
‘Import data from ‘CompLists.txt’ into new Excel file
Workbooks.OpenText Filename:=”C:TempCompList.txt”, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 1), Array(11, 1))

MsgBox “CompList.txt opened”, , “GetNewData”

This MsgBox doesn’t display when the macro is run, but it does if the macro is stepped through.

I don’t know enough about the web stuff (HTML, etc.). I will talk to our IT group for more hand-holding.

But let me answer, and then ask a bit more. The shortcut opens the .MDB, invoking the security from the specified .MDW. I am opening the DAP (.htm) directly (through Windows Explorer, or within IE by the FileOpen dialog).
Can I invoke the security .MDW in a UNC path? What’s the basic syntax for that?

(I won’t ask for a lesson on all this web stuff. I know that this is not the appropriate place for that level of detail.)
Thank you!

Yes, the return value is Currency.
Thanks, again.

The Function worked brilliantly!
I was running into some troubles – and was perplexed – until I finally realized that my Assets field was too large for a variable of type Integer or even Long. When I finally set it to Double, it worked.

Thank you!

That’s probably a good idea. I’ve never done that before, though.
I will look in some books, but if you might give me a simple example of the SQL (or query) that would reference the function, and the function itself, that would be helpful.

Thank you…

SELECT cudata.CU_Nbr, cudata.CU_Name, cudata.State, cudata.Orig_Live_Date, cudata.Nbr_Members, cudata.Assets_Long, 15000+IIf([Nbr_Members]>7500,IIf([Nbr_Members]>20000,(20000-7500)*2.75+IIf([Nbr_Members]>50000,(50000-20000)*1.75+IIf([Nbr_Members]>100000,(100000-50000)*0.75,([Nbr_Members]-50000)*0.75),([Nbr_Members]-20000)*1.75),([Nbr_Members]-7500)*2.75),0)+15000+IIf([Assets_Long]>25000000,IIf([Assets_Long]>100000000,(100000000-25000000)*0.000475+IIf([Assets_Long]>300000000,(300000000-100000000)*0.00025+IIf([Assets_Long]>750000000,(750000000-300000000)*0.00009,([Assets_Long]-300000000)*0.00009),([Assets_Long]-100000000)*0.00025),([Assets_Long]-25000000)*0.000475),0) AS XP2_Lic_Amt, [XP2_Lic_Amt]*0.05 AS Interlinq_Amt, DateDiff(“m”,[Orig_Live_Date],Date()) AS Mos, IIf((DateDiff(“m”,[Orig_Live_Date],Date()))>60,0,(60-DateDiff(“m”,[Orig_Live_Date],Date()))/60) AS Prorate_Rate, ([XP2_Lic_Amt]+[Interlinq_Amt])*IIf([Mos]>60,0,(60-[Mos])/60) AS PR_Amt, xp2_install.xp_Discount_Rate, IIf([xp_Discount_Rate]>0,([XP2_Lic_Amt]-[PR_Amt])*[xp_Discount_Rate],0) AS Disc_Amt, [XP2_Lic_Amt]*0.25 AS P1234_XP2, [PR_Amt]*0.25 AS P1234_PR, xp2_install.xp_PSA_Signed_PSA_Amt, IIf([xp_PSA_Signed_PSA_Amt]<([P1234_XP2]-[P1234_PR]),[xp_PSA_Signed_PSA_Amt],([P1234_XP2]-[P1234_PR])) AS P1_PSA_Cr, IIf(([xp_PSA_Signed_PSA_Amt]-[P1_PSA_Cr])<([P1234_XP2]+[Interlinq_Amt]-[P1234_PR]),([xp_PSA_Signed_PSA_Amt]-[P1_PSA_Cr]),([P1234_XP2]-[P1234_PR])) AS P2_PSA_Cr, IIf(([xp_PSA_Signed_PSA_Amt]-([P1_PSA_Cr]+[P2_PSA_Cr]))<([P1234_XP2]-[P1234_PR]),([xp_PSA_Signed_PSA_Amt]-([P1_PSA_Cr]+[P2_PSA_Cr])),([P1234_XP2]-[P1234_PR])) AS P3_PSA_Cr, IIf(([xp_PSA_Signed_PSA_Amt]-([P1_PSA_Cr]+[P2_PSA_Cr]+[P3_PSA_Cr]))<([P1234_XP2]-[P1234_PR]),([xp_PSA_Signed_PSA_Amt]-([P1_PSA_Cr]+[P2_PSA_Cr]+[P3_PSA_Cr])),([P1234_XP2]-[P1234_PR])) AS P4_PSA_Cr
FROM cudata INNER JOIN xp2_install ON cudata.CU_Nbr = xp2_install.cu_ID
ORDER BY cudata.CU_Name, cudata.State;

Thank you, Wendell, for that helpful information.
Do you know if there is an ODBC or MDAC version that ‘fixes’ this problem?

Your retort, Charlotte, is not helpful — it does not address the real issue of my posting.
I notice that you’re more prone to shoot questions back at people seeking help than you are to provide helpful advice. That’s too bad.

Wendell’s reply to my post was much more helpful. He started off by addressing the issue that I brought up — not some side issue. Only after he addressed the core issue did he make a comment about the Relationship issue.

Now, to answer your question, the relationships are a hold-over from before I upsized from Access to SQL Server. And I have kept them only for convenience — they are especially helpful to other users when doing ad-how querying.

The VBA routine worked brilliantly.
I have not tried the other suggestions (yet).

This is fine for cleaning-up data. But I’d still prefer to avoid filling a cell with something that later needs to be cleared (after pasting special as value). So if anyone knows how to do that in a formula within a cell…

I thank you both for your responses. You’ve made my life easier, today.

Weird: when I update the tables with Linked Table Manager, the SQL Server table updates correctly but the SQL Server View drops the PK designation.
So, every time I update this linked SQL Server View (as a table in Access) I end up having to delete the linked table (deleting any relationships along the way) and then re-link it from scratch.

I think this is a BUG in MS Access!

(BTW: I have to update the link because I made to modify the base table — add a column or something.)

If anyone has words of wisdom or guidance for this situation, I’d love to read them!

OK. I don’t have a ready code example for that (hopefully someone else reading this will).

You need to capture the ‘no matching record’ error — open the 2nd form to add a new record and supply the ID.
(I’ll go look for some code; perhaps some other Lounge Lizard will beat me to it.)

I figured it out!
When I linked the View, somehow I did not designate a Primary Key column. Without a PK, you cannot update the table.

Thanks for all your help, everyone!

Sounds like a ‘find specific record’ situation. Using the Wizard to create a button that opens another Form has an option to ‘find a specific record’ and produces code like the following:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = “My_form”

stLinkCriteria = “[MyID]=” & Me![MyID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Let us know if I’m not addressing your actual situation.