Posts by rmullen

Re: Yes/No Check box

Instead of having a yes/no check box, you may just want to do a message box...

If (code to see if Column T has any negative numbers in it) Then


theAnswer = MsgBox("Is Everything Correct?", vbYesNo)


Select Case theAnswer
Case vbYes
...do rest of program
Case vbNo 
         Exit Sub
End Select


Else


Exit Sub

I have a excel template file (.XLT) with some macros and user forms.

When a user clicks on a button, I want the template file to be opened as a 'New" excel workbook. Thing is, I don't want my users to have to name the file before it is opened.

Any way how I can do this?

Here is the code I'm using:

Set fso = CreateObject("Scripting.FileSystemObject")
    
        'define the template file
        strTemplateFile = CurrentProject.Path & "\MyExcepTemplate.xlt"


        ' Copy template to the target
        'this is the part where i don't want user to input file name though...just where it does it all automatically
        fso.CopyFile strTemplateFile, strOutputFile, False
        
        Set objXL = New Excel.Application
        objXL.Visible = True
        'make sure that you set the file to be editable or else it clones the name and you can't
        'call ThisWorkbook.Path in the excel template successfully
        Set objWkb = objXL.Workbooks.Open(strOutputFile, , , , , , , , , True)
        objWkb.Activate

Any help/ideas here would be sooooo extremely helpful! Thank you so much in advance!

Re: Proper flow of query by Form

That error occurs when you have not defined a parameter ... make sure everything is spelled correctly ... if you can, can you attach the database (strip out all info that isn't necessary)?

Re: form filter to find a record between 2 dates

you want to base your form/subform off the query

"SELECT * from myTable where Table.Date >=#" & StartDateBox & "# AND Table.Date <=#" & EndDateBox & "#"

or whenever the user pushes update. . base the recordset of your form off of that query..make sense?

hi all - I have a switchboard and I'm trying to open a file when my user clicks on a button. Problem is that there are spaces in the file name (I can't change this - it's just how it is). So i was wondering how to do this ... Right now I have

openDW = Shell("MSAccess.exe F:\JQ\Utilities\Just Quarterly\Database Application Program.mde", 1)

and this obviously throws errors because of the spaces.

any ideas on how to do this correctly?

thanks in advance

Re: beforeupdate : request confirmation

you almost have it .....

result = MsgBox("stuff here", vbYesNo, "Confirmation")
'you'll probalby want to use YesNo because it makes more sense than OkCancel if i were the user


Select Case vbYesNo


Case vbYes
           'donothing and continue
Case vbNo
          'prevent the form to be changed'


End Select

this make sense?

Re: Making view/edit modes for a database

How do you have your database set up? If you are running on SQL Server, you need to make two groups. One for editing, and then one for reading .. that way only a few people can edit and a few people can read. That would be the most practical way and easiest to do i think. .... if you don't have access to sql server, then you'd probably want to have a table of users in a table

Table
----------------

UserName: Text
ReadWrite: Boolean

Whenever the open up the database, have a check box that they can check if they want to read (0) the DB or write (-1) to it ... map that field to the ReadWrite field in your table.

On close of that form set a global = Dlookup(ReadWrite, table, Username = CurrentUser).

Then after update of every cell .. if the global < 0 then allow the update .. if not .. then don't allow the update.

make sense?

Re: Data suitable for access?

Building a database is up to you ... you could just build some forms in your spreadsheet to update the information. How difficult to build database? Not very if you've worked with Access before. I'd talk to the mods here to get a quote on how long it would take them to do something like that if you don't have the time.

Re: OnClose Assistance

well .. you can do one of two things ...
on the button push ... do a MainMenuForm.Visible = False
then on the UNLOAD of the new form do a mainmenuform.visible = true ...

you also could do the open and close of the form .. but try it OnUnload of the form instead of OnClose (and if you've already done that i'll just shut up and go sit in my corner)

Re: Switching from Excl to Access

if you don't know much about access .. you may want to get your hands on the Access Developers Handbook (both Enterprise edition and Desktop edition because both cover things that you definitely will need). a few month ago .. all i knew about access was that it was a microsoft product - it's extremely easy to pick up (specially if you've been working with excel). but here is definitely a good place to post and ask questions because the people who run this board are *awesome* (and really smart too - which helps a lot).

Re: OnClose Assistance

not quite sure .. but you may just want to do DoCmd.OpenForm "main form" and don't do the acDialog thing at the end...

I'm trying to show all of the records for a month on a form.

On "SelectDate" form, I have where the user can select whatever date they want to. Then on the next form, i want it to display a month's worth of data ...

so if they select 1/2/2006 - it would display the ENTIRE month of january.
if they select 4/5/2005 - it would display the ENTIRe month of April of 05

Is there any way to do this? I think i'd need to do it on my underlying query for the form, but i'm not sure...

Thanks for your help in advance,
Rebekah

Re: populating a &quot;dynamic&quot; combo box with today's date -5

That works great! Thank you so much!
just a question for future reference on my part ... does the semi-colon represent the separation of each record in the string, or is that just part of the DateAdd() function?

Thanks again!!
Rebekah

I am trying to fill in the fields of my combo box in access.

this combo box really isn't bound to any table, but i'd like it to contain today's date and the 5 previous days to today's date ie DateAdd("d", -5, Date).

I can't figure out how to put this in the combo box though. Do you guys have any suggestions? Thanks!
Rebekah

Re: Creating and saving a file to user defined place

ok so here is what i'm doing now ...

Private Sub txtExcel_Click()
Dim openDW As Integer
Dim loddy As String
Dim Program As String
Dim Directory As String


loddy = Me.txtFileName & ".xls"


'so somewhere about here i need to get the new path ... OTHER than the static path i have set so other people can use this as well...
Call ExportToExcel("C:\Documents and Settings\rmullen\Desktop\" & loddy)


End Sub


Sub ExportToExcel(strOutputFile As String, Optional boolSuppressMessages As Boolean = False)
    
    Dim strTemplateFile As String
    Dim fso
    Dim cnn As ADODB.Connection
    Dim rs As Recordset
    Dim sqlCmd As String
    Dim iRow As Integer
    Dim objSht As Excel.Worksheet
    Dim objWkb As Excel.Workbook
    Dim objXL As Excel.Application
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    


        'define the template file
        strTemplateFile = CurrentProject.Path & "\myExcelTemplate.xlt"


        ' Copy template to the target
        fso.CopyFile strTemplateFile, strOutputFile, False
        
        Set objXL = New Excel.Application
        objXL.Visible = True
        Set objWkb = objXL.Workbooks.Open(strOutputFile)
        objWkb.Activate


    
End Sub

Re: Creating and saving a file to user defined place

Ok .. so in theory

GetSaveAsFilename will produce a popup box ... how do i tell the computer to save myExcelTemplate.XLT as whatever filename they type in?

I know this can be done .. but i'm not sure what keywords to google or even the right words to ask .. so here is what i'm trying to do.

I have an excel template file.

Whenever the user clicks on a button (in an ms access form), I would like a dialog box to open up, the user then can browse to wherever on his computer and save the file under a certain file name.

In reality, they are just going to be doing a "save as" with the excel template file *i think*.

I also have another place in my file, where my user is creating a file on button push (in excel) ... whenever he pushes the button, i'd like a popup box to come up where he can browse to the right place and save the file as whatever he wants to.

can this be done?

any clue on how to do it?

Thanks much in advance :thanx:

Re: Extracting data from access to many sheets simultaneously

lol well i don't know what's wrong with it w/o looking at what you're trying to import. the database that i work with has about 700 tables in it, and when i'm streaming data from access into excel, there's no problem ...

what code are you using?
are you using filesystem objects?

Re: Extracting data from access to many sheets simultaneously

Hmm .. can you paste the code that you are using?

and are you trying to export data from access to excel ...
or import data from access to excel?

I know there is a one line thing to convert a number into a date .. and i would like to know what it is!

objSheet.Cells(iRow, i) = rs!TDate

is what i have so far. this produces the correct number for the date, but i'd ideally like it if i could just magically format objSheet.Cells(iRow, i) to be a date.

Thanks for any help!

Rebekah