Replies

Charlotte,
The data will be for 12,000 units plus.
I didn’t really want to have a table that big, but I will give it some thought.
Any other ideas welcome
Thanks, and thanks Francois

Roberta,
Here is a small sample DB that has the open dialog API example for you.

Mark,
How would that show a 6 month total(sum) for each machine?
It seems to me that I would somehow have to keep 6 months of readings for each machine in the table and then delete the earliest reading when the next month rolls around(once I get at least 7 months worth of data)
But not sure how to do this.
Thanks,
Scott

My 2 cents worth
In control panel under regional settings make sure your short date format is set to MM/dd/yyyy
I had some date issues when the setting was at the default which I think was M/d/yy

Here is a routine that opens your forms and centers them on the screen.
Place this in the declarations section of a module
Global g_ScreenWidth As Integer ‘ Screen width for CenterForm sub
Global g_ScreenHeight As Integer ‘ Screen height for CenterForm sub

Here is the function:
Sub CenterForm(p_Width As Integer, p_Height As Integer)
‘ Arguments are in twips where 1 inch = 1440 twips
‘ If form has title bar, add 240 twips to height argument
‘
‘ Resolution Dimensions Title Bar, Menu Bar, and Tool Bar):
‘
‘ Resolution ScreenWidth ScreenHeight
‘ 640×480 9600 5720
‘ 600×800 12000 7400

Dim ScreenWidth As Integer
Dim ScreenHeight As Integer
Dim VertPos As Integer
Dim HorPos As Integer

‘ Set ScreenWidth & ScreenHeight according to resolution (see table above)
ScreenWidth = 12000
ScreenHeight = 7400

If p_Height > ScreenHeight Then
VertPos = 0
Else
VertPos = (ScreenHeight – p_Height) / 2
End If

If p_Width > ScreenWidth Then
HorPos = 0
Else
HorPos = (ScreenWidth – p_Width) / 2
End If

DoCmd.MoveSize HorPos, VertPos, p_Width, p_Height

End Sub
Then in the OnOpen Event of the form use
CenterForm 2880, 2880 ‘If your form was 2″ x 2″

Why not just put the form in the startup options.
Click Tools.. then startup .. then enter the form name

Thank you that did the trick

Hi,
The column names are different manufacturing companies
the rows are their model numbers for the item
Each company has its own model number even though the item is the same
eg. sorry doesn’t format properly
company1 company2 company3 company4
tl32c ft2343 2345 widget1
rd4565 pp1221 rr45 344r
I want to be able to lookup say “widget1” and have the db return that whole row so the user
can see what the equivilent models are
Hope this helps

I found out what the problem was
ObjectType in the TransferDatabase method needed to be a number, not a string (acModule,acForm…)
I used a Select Case statement to provide the numbers

Dim strSQL As String
Dim rs As Recordset
Dim db As Database
Dim dbs As Database
Dim rst As Recordset
Dim targetws As Workspace
Dim targetdb As Database
Dim strNME As String
Dim strTYPE As String

””DELETES ALL EXISTING OBJECTS EXCEPT TABLES

Set db = CurrentDb
strSQL = “SELECT MsysObjects.Name,
IIf([TYPE]=-32768,’acForm’,IIf([TYPE]=-32766,’acMacro’,IIf([TYPE]=-32764,’acReport’,IIf([TYPE]=5,’acQuery’,IIf([TYPE]=-32761,’acModule’)))))
” & _
“AS Expr1, MsysObjects.Type FROM MsysObjects WHERE (((MsysObjects.Name)’frmUPGRADE’) AND ((MsysObjects.Type)1 And
(MsysObjects.Type)2 And (MsysObjects.Type)3 And (MsysObjects.Type)6 And (MsysObjects.Type)-32757) AND
((Left$([Name],1))’~’)) ” & _
“ORDER BY MsysObjects.Name;”

Set rs = db.OpenRecordset(strSQL)
rs.MoveFirst
Do While Not rs.EOF
Select Case rs(1)
Case “acMacro”
DoCmd.DeleteObject acMacro, rs(0)
Case “acForm”
DoCmd.DeleteObject acForm, rs(0)
Case “acQuery”
DoCmd.DeleteObject acQuery, rs(0)
Case “acReport”
DoCmd.DeleteObject acReport, rs(0)
Case “acModule”
DoCmd.DeleteObject acModule, rs(0)
End Select
rs.MoveNext
Loop
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, “tblVERSION”
DoCmd.SetWarnings True

””IMPORTES NEW OBJECTS FROM MODIFIED DB
Set targetws = DBEngine.Workspaces(0)
Set targetdb = targetws.OpenDatabase(“c:reliability_upgrade.mdb”)
Set dbs = targetdb ‘REFERENCE TO THE EXTERNAL DB

strSQL = “SELECT MsysObjects.Name, IIf([TYPE]=-32768,’acForm’,IIf([TYPE]=-32766,’acMacro’, ” & _
“IIf([TYPE]=-32764,’acReport’,IIf([TYPE]=5,’acQuery’,IIf([TYPE]=-32761,’acModule’, ” & _
“IIf([TYPE]=1,’acTable’)))))) AS OBJ, MsysObjects.Type ” & _
“FROM MSYSOBJECTS ” & _
“WHERE (((MsysObjects.Name) Not Like ‘msys*’ And (MsysObjects.Name)’FRMUPDATE’) AND ” & _
“((MsysObjects.Type)2 And (MsysObjects.Type)3 And (MsysObjects.Type)6 And ” & _
“(MsysObjects.Type)-32757) AND ((Left$([Name],1))’~’)) ” & _
“ORDER BY MsysObjects.Name;”

Set rst = dbs.OpenRecordset(strSQL) ‘IDENTIFIES ALL OBJECTS IN THE EXTERNAL DB TO IMPORT

Do Until rst.EOF
Select Case rst!OBJ ‘SUPPLIES THE VALUE OF OBJECTTYPE AS A NUMBER
Case “acForm”
strTYPE = 2
Case “acMacro”
strTYPE = 4
Case “acModule”
strTYPE = 5
Case “acQuery”
strTYPE = 1
Case “acReport”
strTYPE = 3
Case “acTABLE”
strTYPE = 0
End Select
strNME = rst!NAME
DoCmd.TransferDatabase acImport, “MICROSOFT ACCESS”, “C:RELIABILITY_UPGRADE.MDB”, strTYPE, strNME, strNME
rst.MoveNext
Loop
targetdb.Close
MsgBox “Done”
Kill “c:reliability_upgrade.mdb” ‘DELETE EXTERNAL DB