Replies

Thank You Hans

For now hold shift key when opening. The form called “frm_Report_Criteria” opens the parameters for the report “rpt_Op_Log_2”. If you select 11/21/2009, 400, 401, and 503843 in the drop downs respectively. This should find a record meeting that criteria in the “tbl_Inputs_Outputs” table. In the On Print event of that report you will find the dsum calc’s.

Thank you again,
Kevin

I think all that was wrong was that you left out the closing ” after the #. When using multiple criteria I prefer to put brackets around each condition.

vInPut430TarsSun = Nz(DSum(“[Input 430 TARs]”, “tbl_Inputs_Outputs”, “([badge number] = ‘” & Forms![frm_Report_Criteria]![vEmployee] & “‘) and ([Activity Date] = #” & vSun & “#)”), 0)

I find it useful to introduce a string variable for the criteria. This helps with the debugging.

Dim strwhere as string
strwhere = “([badge number] = ‘” & Forms![frm_Report_Criteria]![vEmployee] & “‘) and ([Activity Date] = #” & vSun & “#)”
Debug.print strwhere
vInPut430TarsSun = Nz(DSum(“[Input 430 TARs]”, “tbl_Inputs_Outputs”,strwhere), 0)

Thank You very much for the input.

I no longer get any syntax errors. However in this case I do not get the right result either. For testing purposes, the table this dsum hits has three records in it. One of them should be found by equation. What seems to be happening is the result is evaluating to Null and therefore converting to zero. When I step through and toggle a breakpoint after the equation, each individual criteria contains the correct result but the the combined criteria is failing. Would you have any thoughts as to why?

List boxes in Microsoft Access do *not* have a TextAlign property. They are left-aligned by default.

You’d need Windows API functions to change that; Stephen Lebans has a sample database with the necessary code: JustiCombo.

Thanks Hans,

Its probably easier to use a continuous subform for now. I appreciate your help.

HANS,

Sorry, I did a search in the ACCESS topic but it must have brought up the Excel. This is an Access question. My bad. Still possible?

Hans,
I can’t be in the same place you are unless it is a difference in versions. I am using Office 2003 SP 3. I can see everything else you are showing in your screen shot except textalign.

In the Visual Basic Editor, open the userform.
Click on the list box to select it.
In the Properties pane, set the TextAlign property to 3 – fmTextAlignRight.

My properties for that list box do not show a text align property

Is it also possible to right justify data in a list box? I have a query populating a list box with one field shoing standard numbers. (i.e. 123,456,789 with no decimals). However it is left justified and the numbers do not lign up if they are different lengths. Can that field be formatted to display as right justified?

It is hard to read, but I think you have a problem with apostrophes in the WHERE portion. It should be:

, “[PIIN_CODE] = ‘” & Forms![supmod -> Mod]![PIIN_CODE] & “‘ And [CLIN] = ‘” & Me![CLIN] & “‘”)

or this:

, “[PIIN_CODE] = ” & chr(34) & Forms![supmod -> Mod]![PIIN_CODE] & chr(34) & ” And [CLIN] = ” & chr(34) & Me![CLIN] & chr(34))

(note: chr(34) is a double-quote.

Thank you,

That worked perfectly. Looks like I had a few too many quotes.

perfect, up and running, thanks

Sorry Hans,

Right after I posted I realized the problem is in the loop, I think. I think I am just setting the vFileCOntents to the last section of the .txt file. I need to combine all into one long string. I think.

Hans,
Thanks, I looked into all that you have stated. I managed to get the following code to work…

DoCmd.TransferText acExportMerge, , “tblEmailChangeListTable”, “C:Documents and Settingsmcneilkm.GROTONMy DocumentsEBPricingFilesUploaded.txt …

I combine it with the following …

Dim vFileContents As String

Import Text File to get File Name

Open “C:Documents and Settingsmcneilkm.GROTONMy DocumentsEBPricingFilesUploaded.txt” For Input As #1
Do While Not EOF(1)
Input #1, vFileContents
Loop
Close #1

DoCmd.SendObject , , , “LarsonBC@supship.navy.mil;mcneilKM@supship.navy.mil”, , , “Pricing and Technical Report Data has been imported to the Pricing Program”, “The Following Change Data has been added to the Pricing Program. ” & Chr(10) & Chr(13) & Chr(10) & Chr(13) & vFileContents & Chr(10) & Chr(13) & Chr(10) & Chr(13) & “This latest Data from EB is through ” & Date & ” ” & Time & “.” & Chr(10) & Chr(13) & Chr(10) & Chr(13) & “If you have any problems please repond to mcneilkm@supship.navy.mil or LarsonBC@supship.navy.mil.”, False …

The only problem I seem to be encountering is that there are three or four lines in the .txt file and only the very last section is making it to the email. I can’t seem to collect all of the contents of the .txt file.
Do you have any suggestions?

Thanks
Kevin

thanks Hans, see attached

Ironically, a vba module opens up (it is a different one every time) and no lines are highlighted.

kevin

I got an error which pointed to the “MSBDR9.OLB” when I initially imported the report I started this thread with (it was a copy of a database that was converterted from 97 to 2000 to 2003). The import was not successfull and it automatically created a table for autosave failures with an entry record that said it could not open the report. After posting the issue an deciding to try to import from the 97 database, it seemed to import OK. Then as I was making changes to the report to fit the requirements of the current database, and tried to compile, I got a “Missing Module” error. As soon as I got that error I could no longer compact and repair or finish compiling. If I just import the file from the original database (97) and don’t make any changes to it, I don’t get any errors. Does this help?

OK, this is strange to me. When I look in the database that I imported to, there is no “Missing” reference. When I look at the database imported from, there is a “Missing” Reference. I cannot simply uncheck/check because the file doesn’t actually exist on my computer that I am aware of. I tried browsing for it from the reference dialog and cannot find it. I presume this is because it does not come with the office 2003 software.