Hello,

I have a form and subform.  After a requery, when I am not able to move to the bookmark.  I am getting an error "Not a valid bookmark".  Please advice.


        Dim sf As Access.Form
        Set sf = Forms![F_Maint_Check_Main]![F_Maint_Check_Vol_SoftAR_EC_New].Form
             
             Dim value As Variant
              value = sf.Bookmark
                   
                     sf.Requery

                         sf.Bookmark = value

So I am exporting data from an Access 2013 Query to an Excel 2013 Template.
The export works out great.

The issue I am running into is wrapping text on a select range after the export via VBA from Access.
I can wrap text on the whole sheet just by doing the following:

oExcelWrkSht.Cells.WrapText = True

Select all Open in new window

My starting row will always be 14 (A14) and my ending column will be 14 (M14).
But the range can be anywhere from 6 to 600.

I have added code that tell me the last row used:

lLastRow = oExcelWrkSht.Cells(oExcelWrkSht.Rows.Count, "A").End(xlUp).Row

Select all Open in new window

Which on my test is 30.

So I want all cells between  A14 and M30 to be wrapped.
I tried:

oExcelWrkSht.Range(lStartRow, lLastRow).WrapText = True

Select all Open in new window

And I get an error, 1004

Thanks for any help

Attached is the full code.
Excel-Export.txt

Hello,

I have a form and subform.  After a requery, when I try to gotorecord in the subform, sometimes the record is in the bottom of the form.  Below is my code... how do I always have the record show up on the top.

Dim sf As Access.Form
        Set sf = Forms![F_Maint_Check_Main]![F_Maint_Check_Vol_SoftAR_EC_New].Form
           
            Dim value As Integer
            value = sf.CurrentRecord
                               
                sf.Requery
               
                   DoCmd.GoToRecord , , acGoTo, value
                       DoCmd.Close acForm, "F_Maint_Master_Edit_Button", acSaveYes

Hi, i have a form where we pass string say aaa bbb ccc through inputbox function and enter a value say 2
then in the text box it needs to display bbb as we entered value 2.
if user entered value 3 then it needs to display ccc
i tried some vba code but it didin't get me desired value
  Dim i As String
   i = InputBox("Enter Values")
   Dim j As Integer
   j = InputBox("Enter number")
Me.txtbox.Value = Mid(i, j)
 this is returning aa bbb ccc in the box
i tired using split function as well but didn't got required answer
can someone help me with this code
thanks in advance

I have a MS Access form using VBA for a field after the update.  The code is checking for a character in another field to determine what code is executed:

   
    If InStr(Nz(Me.LNotes, 0), "ß") = 0 Then
        Me.LastStatus = Now()
    Else........

All is working fine however I just noted that if the field (LNotes - memo field) has the word "Processing" the initial code in the IF statement is NOT executed even if the character ß is not present.

Any idea why this might be?  I have been using this code for years.

I need to know if i can pass a string to a stored procedure in sql server ?

 I have a small routine that i use in Access vba to build a where statement
The values come from an Unbound textbox on a form

Nf1 = ""
cD = ""
cD = Me.Text0    'unbound textbox called  "text_search" 
    lk = "Like"
    lm = "text_search"
 
    cD = Trim(cD)
    
    ' how many items are in the cell ?  build an array
    cArrSql = Split(cD, " ")
        
    ' search through the cell requests tokens.
    For x = LBound(cArrSql) To UBound(cArrSql)
        If x = LBound(cArrSql) Then
            Nf1 = "WHERE " & lm & " Like " & Quote("%" & cArrSql(x) & "%")
        Else
            Nf1 = Nf1 & " AND " & lm & " Like " & Quote("%" & cArrSql(x) & "%")
        End If
    Next
 
    BuildWhere = Nf1

using this for single or double quotes:
Function Quote(ByVal strText As String)
  '  Quote = Chr(34) & strText & Chr(34) ' double
    Quote = Chr(39) & strText & Chr(39)  ' single
End Function

Select all Open in new window

I may have only 1 value or sometimes many values typed in text0:
i.e.  Glove  XL
I.E.  12324   HBO

What I need:

I need to pass this "where clause" as a string to a stored procedure to sql server someway ?

The where clause when built looks like this :

Where text_search like  '%guardian%'  and text_search like '%481361%'  


I want to pass to sql server:
@string   text_search like  '%guardian%'  and text_search like '%481361%'

My procedure in sql server is:   a basic select statement
select       [EstimatedFreightCost]
      ,[FreightOverrideFlag]
      ,[ForwardingAgent]
      ,[text_search]
  FROM [ss_prog].[dbo].[Quote_Detail]
 
  where  + @string +
 
  (this is not working)
 
 

Thanks
fordraiders

Hi Guys, There must be a better way to move controls at runtime.
The question is how I reposition a textbox or label control at runtime. I’m using Access 2010.
I have a 7 reports that do roughly the same thing that is they prints 3 rows, a label, a quantity and a cost, in the details section of the report.
Please the layout attached

 

If say the 3rd and 4th column is empty I need that layout to as below so in this case the 5th column gets moved the 3rd column position.
 
I’m using the reports Detail_Format event to trigger the code.
I’m not going in detail at this point because I’m hoping someone has a simple solution.
But I’m drowning in code.

Regards
cornholme
Three-FiveColumns.png

Access Error: Class does not support automation or does not support expected interface

Sub PasteNewGeogData()

Dim xlWB As Object

Dim strPath As String
Dim strFileName As String

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim fieldValue As Long
Dim xLSheet As Variant
Dim qdf As DAO.QueryDef

On Error GoTo After_Error

strPath = "\\Mac\AAIM\Portfolios\"
strFileName = "GraphAccessGeogData.xlsx"
Set xlWB = Nothing

If IsValidOLELinkEstablished(xlWB, strPath, strFileName) = True Then
 
        'Set worksheet
        Set xLSheet = xlWB.Sheets("Sheet2")
        xlWB.Application.Calculation = xlManual
        xlWB.Application.Screenupdating = False
        'Get reference to database and relevant query
       
        Set db = CurrentDb
       
        Set qdf = CurrentDb.QueryDefs("qry_UnitsByCountyForSelectedHA")
        qdf.Parameters("TempVars!HASelected") = TempVars("HASelected").Value
        Set rs = qdf.OpenRecordset()

       
        xLSheet.range("A1").copyfromrecordset (rs)  'This gives the error

I have a field on the main form.  When I exit that field I want the tab to go to a specific field on the subform.  Here is my code but it doesn't work:

Forms!frmMainForm!subfrmSubForm.Form!txtAppointmentDate.SetFocus

What is wrong with this code?

I want to use/refer to a Public Const (pubAccNo) in a textbox on a report (see example below) - but when running the report it asks for the value of the constant - can global constants not be referred to on a report ?

ControlSource = "Accountnumber: " & pubAccNo

I have a filed that contain a name and employee number.  I import this data from excel to access. I would like to create a update query to separate the code from the name, the text filed look like this, Bob Helmond (ID 24177). I need to be able to extract the code of 24177 in a new column called employee id. it can be done after the data is imported. I don't want to include the word ID in the code. just the numeric number.

the filed currently is called name, the table is called, tbl_Exam,

thank you