Anyone good with Access 97 ?

Hi everyone, small problem. I want to make a support / call logging program for myself to help me at work, but am stuck on the very first part!!!

As soon as access loads, a form is opened up. This form, when run, creates a table called "Week Ending :" & Date -where date is always the last day / Friday of the week.

Please can you tell me where the below is going wrong? I am trying to create a new table with Access DAO with 2 column headings.

Thank you every one ;)

Code:

---

Private Sub Form_Load()
    Dim Newfld As Variant, Newfld2 As Variant
    CurrentDb.CreateTableDef (Date)
        Set Newfld = CurrentDb.TableDefs("Week Ending :" & Date).CreateField("Customer", "text")
        Set Newfld2 = CurrentDb.TableDefs("Week Ending :" & Date).CreateField("Support Issue", "text")
        Set Newfld = Nothing
        Set Newfld2 = Nothing
End Sub

---

why you dont try this code before use de access command.

Dim appAccess As Access.Application
Dim sPath As String
sPath = "path"
Set appAccess = _
CreateObject("Access.Application")
appAccess.OpenCurrentDatabase sPath
appAccess.DoCmd.OpenForm "Main"

One thing: Do you know how can i create and EXE file in ACCESS or something better than a MDE?

Thank you Luis, I should have explained better.

This bit I am Ok with, thank you for the answer though. I have just taken the part of my code which has the error in to show as an example.

As to your question, MDE is the highest level. If you want this to appear as an exe file, you can hide all of the menus, toolbars etc by going to the tools menu and selectijng the startup option.

[Edited by alex_read on 10-20-2000 at 10:06 AM]

This will do the job. It creates a table name todays date (ie 10202000 for Oct. 20, 2000).


Private Sub Form_Load()
Dim dbs As Database
Dim strTableName As String
Set dbs = CurrentDb
strTableName = Month(Date) & Day(Date) & Year(Date)
dbs.Execute "CREATE TABLE " & strTableName & " (Customer TEXT, Support_Issue TEXT );"
dbs.Close
End Sub

Oops, don't forget the error checking!! As for a table being named "Week Ending: 10202000", I do not belive that is a valid table name. You should be able to name it "Week_Ending_10202000" though; but to me less typing is better so I would prefer the 10202000!!


Private Sub Form_Load()

Dim dbs As Database
Dim strTableName As String
On Error GoTo getout

Set dbs = CurrentDb
strTableName = Month(Date) & Day(Date) & Year(Date)
dbs.Execute "CREATE TABLE " & strTableName & " (Customer TEXT, Support_Issue TEXT );"
dbs.Close

getout:
If Err = 3010 Then
MsgBox "Table " & strTableName & " exists."
dbs.Close
Exit Sub
Else
MsgBox "Table " & strTableName & " created."
End If
End Sub

Thank you for everyones help. Worked a treat !

Thank you everyone for the responses. Just to be aawkward (because it's sokmething I'm good at) :D Is there any way to split the date up please?

I am trying :

Code:

---

    Dim strTableName As String
    strTableName = "Week Ending_" & Month(Date) & "_" & Day(Date) & "_" & Year(Date)
    CurrentDb.Execute "CREATE TABLE " & strTableName & " (Customer TEXT, Support_Issue TEXT );"
    CurrentDb.Close

---

without much luck. If possible, can you have 10.01.00 or 10/01/00 or 10_01_00 etc?

Thank you once again! :)

This will give you the format of mm_dd_yy


strTableName = Month(Date) & "_" & Day(Date) & "_" & Mid(Year(Date), 3, 4)

Thank you dangr, I have it now :D

Why do you have to split the date when you can use Format function which will do it for you:

strTableName = Format(Date, "mm_dd_yyyy")