Search results

  1. T

    Can't set focus error or OpenForm action canceled error

    Hi All, Apologies for the bad explanation. I have a some code used to test frmChemoRecord, it loops through various scenarios completing the form with a range of different values to test the output. The problem comes when it gets to the DoCmd.OpenForm "frmDateSelect", acNormal, , , ...
  2. T

    Ignore query criteria if form not open

    Hi All, I have multiple forms for entering different types of stock onto a database, when entering, a query looks up stock already on the database for that product. To make life simplier I want one query with the criteria for product something like: [Forms]![frmStockEntry].[Product] Or...
  3. T

    Run time error 13 on End Function

    Hi All, I have a function that I want to export as an array. I've limited the code to the Type Mismatch error Public Function BatchProductionVial1(DrugName As String, DrugRoute As String, DrugDose As Double, DrugBatchSize As Integer) As Variant Dim returnVal(3) As Variant...
  4. T

    Dates Confusion in VBA

    Hi All, I had a bit of code that ran in the run up to Christmas. If Date >= Format("01/12/" & Year(Date), "dd/mm/yyyy") And Date < Format("25/12/" & Year(Date), "dd/mm/yyyy") ThenThis didn't work after the 1st December passed, the solution was Format(Date, "dd/mm/yyyy") >= Format("01/12/" &...
  5. T

    iif statement with query criteria

    Hi All, I have a Form frmAuditTrail, it has three combo boxes in the header (Username, Area & Action) What I'm attempting to do is if an Area is selected then I want the Combo box for Action to only list Actions associated with the Area. The difficulty comes when if no Area is select then I...
  6. T

    Continous Form vba changing label

    Hi All, I have a continuous form for entering drugs for each drug it has a combobox to select frequency (Day, Week, Month, Year) I would like the result of this field to change the label for just that record. There's a bit of vba that adds and "s" and changes the unbound text box. I did...
  7. T

    Use VBA code to Replace other VBA code

    Hi All, In brief I need to press a button that will disable the MsgBox's, then I need the database to run through a large set of test data to check the output. In normal circumstances the operator would be present, but for testing I just need to run the data and review the results without it...
  8. T

    Altering SQL Linked table from VBA

    Hi All, Recently upsized Access Db to SQL Server. One issue we have is if somebody added a new drug to the access database it creates a new column in the Access table. CurrentDb.Execute ("ALTER TABLE tblDrugListOral ADD COLUMN " & DrugNameWithOutSpace & " Number;") Problem is now the...
  9. T

    Update SQL for timestamp only treatments that are ready

    Hi Folks, We have a tracking system for chemotherapy when all the treatments for that day are ready an UPDATE SQL query is run to set the status of all patients drugs to ready for collection. ReadyForCollectionSQL = "UPDATE tblChemoRecord SET tblChemoRecord.ReadyForCollection = Now() "...
  10. T

    Error handling with loop

    Hi All, I've got a problem with a loop for producing worksheets (reports). When everything works there's no problem but when one of the reports namely rptChemoOralWorksheet doesn't have data available to produce the report the loop finishes and then all remaining worksheets don't print. I've...
  11. T

    Removing text from string using Replace() & wildcards

    Hi Guys & Girls, I have a list of consumables; Syringe 50ml Syringe 20ml Syringe 5ml Syringe Cap White Needle I want to remove only the number and the ml part from the list, so I would end up with; Syringe Syringe Syringe Syringe Cap White Needle If I use...
  12. T

    Best way / work around around to handle a zero results query

    Hi Ladies & Gentleman, I have several create table (atblStockLiveShortExpiry1) queries that where stock is required (& available) returns a result that is the shortest dated stock to be used later in my code. I'm now looking to change the code so that the manufacturers in each of the created...
  13. T

    Struggling with SQL in VBA

    Hi Folks, I've created an update query in access and it works as expected: UPDATE atblChemoPreCompoundOrder SET atblChemoPreCompoundOrder.Patient = [Forms].[frmChemoRecord].[Patient], atblChemoPreCompoundOrder.[Patient Number] = [Forms].[frmChemoRecord].[Patient Number] WHERE...
  14. T

    Problem with DLookup

    I'm struggling with what should be a simple problem: This works: PreCompoundedLookupUnused = Nz(DLookup("[DoseRequired1]", "atblChemoPreCompoundOrder", "[Preparation]='" & Me![PreparationNotVisible] & "' And StockReceived = -1 And StockUsed = 0 And NextCycleDue < Date-8"), "")However when I add...
  15. T

    Scanning barcodes to track items while using other database functions

    Hi Guys, I can use barcode scanning to track items through the process, on the condition that the relevant field on a form is highlighted. The issue is I want the scanning/tracking with wireless scanners to be going on in the background while an operator can use the other functions of the...
  16. T

    Create table for top/min record only

    Hi Guys, I have a problem with a make take sql query. I have the make table query below Dim sql1 As String sql1 = " SELECT TOP 1 [Forms]![frmChemoRecord].[BatchNumber] AS BatchNumber1, qryStockLiveUse.DrugNameVial AS DrugNameVial1, qryStockLiveUse.Product AS Product1...
  17. T

    Building an array if file exists

    Hi Guys, I'm looking for a way to build an array of file names where a file exists. (I've been advised this is the way to sort my problem) The list of potential file names will be from a query. The array will be used to attach PDFs to an e-mail The problem is that I'm really novice with...
  18. T

    Groupwise Adding a list of attachments

    Hi Guys Hoping to rack your brains. I've sorted the multiple attachment problem. It's base on a qry such as: E-mailAddress_________Attachment1_________Attachment2 ?????????????_________C:\XXXXXXX_________C:\XXXXXXXX My issue is I have a list of pdf's which I can easily convert to a...
  19. T

    DSum not producing correct result from Date range

    Currently being challenged by the DSum function. I have the following table tblWastage ID WastageDate TotalCost Count 8 30/11/2011 £102.44 1 9 30/11/2011 £102.44 1 10 30/11/2011 £28.69 1 28...
  20. T

    Report Subtotal linked to query criteria for subreport?

    I've got a report "rptSummary" that shows drug names + number of uses and the sum of there cost over a fixed period. This is all calculated through a query "qrySummary". The report "rptSummary" has a calculated field to give the total of all the drug costs over that time period...
Top Bottom