Posts by gibbo1715

  • Merge two tables

    Re: Merge two tables


    Hi Norie,


    Thanks for the reply


    The reason I ask for code is that i am using automation and accessing my database from either excel (Mostly) or VB so not from within access


    Cheers


    Gibbo

  • Merge two tables

    All


    Can anyone tell me how (or if) I can merge two tables into one please, but i want to be able to do this via code.


    The two tables are in different databases but have an identical structure.


    Cheers


    Gibbo

  • Transferring data between Access/Excel

    Re: Transferring data between Access/Excel


    Nat


    Have a look at the attached, its messy but might give you some ideas


    http://www.vbaexpress.com/forum/showthread.php?t=5728


    I had a similar problem so have been learning about opening access from within excel and my users only need to see excel (Called Automation)


    Note: You do not need access installed to create or deal with data contained in one


    Anyway have a look and see what you think


    Might give you some ideas


    Gibbo

  • Edit access record

    Re: Edit access record


    Ok got both bits of code working now as follows



    Thanks for the help


    Gibbo

  • Edit access record

    Re: Edit access record



    I get an error arguments are out of acceptable range or are in conflict with each other


    Any Ideas


    Cheers


    Gibbo

  • Edit access record

    Re: Edit access record


    Ok Norie


    Sure you ll find lots wrong with this as i am very new to this idea


    the code below is to navigate , delete, create and edit (At least thats the aim)


    Thanks for your help


    Gibbo



  • Edit access record

    Re: Edit access record


    Hi Norie, sorry a bit vague


    Excel does not recognise .EditMode.


    Having read a bit more on this subject I dont think it is as simple as that and think what I am actually looking for is a query


    What I want to do is with the record I am viewing from an excel userform, change the content and then save those changes to my access database over-writing the record.


    Can you suggest how i can do this or point me to where I can read up on the subject


    Cheers


    Gibbo

  • Edit access record

    All


    I have a user form in excel that accesses an access database and navigates through the records.


    Can anyone tell me how i can update a current record please


    I tried the code below but get error as it doesnt recognise the command


    Thanks in advance


    Gibbo


    Code
    'edits/updates the current record with values currently shown in the TextBoxes
  rs.EditMode
            .Fields("FieldName1") = Me.TextBox1.Text
            .Fields("FieldName2") = Me.TextBox2.Text
            .Fields("FieldName3") = Me.TextBox3.Text
            .Fields("FieldName4") = Me.TextBox4.Text
            .Fields("FieldName5") = Me.TextBox5.Text
  rstObj.Update

  • Insert Rows Based On Values in a Column

    Re: Insert Rows Based On Values in a Column


    Off the top of my head if you want a button and the amount is next to the part number something like below should get you started, not good code though, you should set it up to find the correct cell on the row automatically but i cant remember at the mo how to do that, sorry


    [VBA]
    Sub Button1_Click()


    Dim i As Long
    Dim Amount As Long


    Amount = ActiveCell.Offset(0, 1).Value


    For i = 1 To Amount


    ActiveCell.Offset(1, 0).EntireRow.Select
    Selection.Insert Shift:=xlDown


    Next


    End Sub


    [/VBA]


    Gibbo

  • update links with a macro

    Re: update links with a macro


    Try this for starters to give you an idea
    [VBA]
    Range("C8").Select
    Dim i As Integer
    Dim WeekNum As Integer
    WeekNum = Range("A2")
    For i = 1 To WeekNum
    If i < 10 Then
    ActiveCell.Formula = "='M:\Network\indep\supp1\[0" & i & ".xls]RR'!$F$30"
    Else
    ActiveCell.Formula = "='M:\Network\indep\supp1\[" & i & ".xls]RR'!$F$30"
    End If
    ActiveCell.Offset(1, 0).Select

    Next i


    [/VBA]


    Cheers


    Gibbo

  • Search through subfolders for a file type

    Re: Search through subfolders for a file type


    I ve added a little bit to Andys code so you can select the directory you would like to search, this is the shortest method I am aware of (unless anyone knows a shorter one ;)


    Cheers Gibbo


    [VBA]


    Function GetFolderPath() As String
    Dim oShell As Object
    Set oShell = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please select folder", 0, "c:\\")
    If Not oShell Is Nothing Then
    GetFolderPath = oShell.Items.Item.Path
    Else
    GetFolderPath = vbNullString
    End If
    Set oShell = Nothing
    End Function
    Private Sub CommandButton1_Click()

    Dim fscTemp As FileSearch
    Dim lngIndex As Long
    Dim sFolder As String

    sFolder = GetFolderPath
    If sFolder <> vbNullString Then MsgBox sFolder

    Set fscTemp = Application.FileSearch
    With fscTemp
    .FileType = msoFileTypeExcelWorkbooks
    .LookIn = sFolder
    .SearchSubFolders = True
    .Execute
    For lngIndex = 1 To .FoundFiles.Count
    ListBox1.AddItem .FoundFiles.Item(lngIndex)
    Next
    End With

    End Sub


    [/VBA]

  • move based on date

    what i want to have is a userform where i enter a date dd,mm,yyyy and some data and can do this many times


    I want to put the date onto sheet 1 as follows


    Earliest date always goes in column A3( data in A4): and sort all the other dates (and Data) earliest until latest as they are added leaving a gap between each date


    So if i add an earlier date than is already displayed it would add it before that date as follows


    so dates would be A3 - gap - C3 - gap - E3 and so on
    01/01/2003 - 01/02/2003 - 01/03/2003
    Data - Data - Data


    Any help with this is greatfully received


    :?