Help changing FoxPro to VB Code

[That Crazy Hockey Dood]

I am working on the conversion of Visual FoxPro designed forms and processes
into Access 2003. Unfortunately, I am not sure how to make the code used in
FoxPro work in VB.

Here is what happens in FoxPro. A user enters a beginning & an end date on
the "entry" form. The form then writes the entries to the table for all of
the dates that fall in the range. For example: Beginning Date: 6/2/2007
Through Date: 6/10/2007
The data is written to the table for 6/2, 6/3, 6/4, etc.

Here is the code as written in FoxPro for this function:
public diff, count
m.thrudate=m.thrudate+1
diff =(m.thrudate-m.date)
m.day=1
FOR count=1 to diff
select except
Set Order to id
Set Deleted Off
Go bottom
m.id=except.id+1
Set Deleted On
m.lock=RLOCK()
IF m.lock=.t.
INSERT INTO except
(id,type,ssn,name,realtime,date,excused,fmla,note,time,type,initial);
VALUES
(m.id,m.type,m.ssn,m.name,m.realtime,m.date,m.excused,m.fmla,m.note,m.time,m.type,m.initial)
IF m.day=1
=messagebox("First multiple day record was entered",0,"Record Entered")
ELSE
=messagebox("Next multiple day record was entered",0,"Record Entered")
ENDIF
UNLOCK
ELSE
=messagebox("Record was not entered, please try again",0,"Please try again")
ENDIF
****Insert into except from memvar
m.date=m.date+1
m.day=m.day+1
ENDFOR

My hope is that someone out there has already run into this before and it
will be a quick fix.

Thanks,
Jim

 

[Steve Sanford]

Hi Jim,

I used to use dBase 3, 3+ & 4; no one has responded, so I'll give it a shot.

First, you'll should rename some of the field names - many are reserved
words; see this site:

http://allenbrowne.com/AppIssueBadWord.html

Second, the field names are not very descriptive. For example, one field is
"name". Name of what? Another field is "date". Date of what?? Effective date?
Date of birth?

Also, there are two fields named "Type".


IIRC, the "m." is a memory variable.

Q: Where does the data come from for the memory variables?

Q: If you have 5 new dates (records) to enter, are all records created with
the same data wxcept for the "id", and "date" fields?

Q: What are the data types of the fields?
This is what I guessed:

'this assumes these data types in table 'Except':
'field datatype
'------------------
'id Long
'Type1 String *** Access reserved
'ssn String
'TheName String *** Access reserved
'realtime Date/Time
'Thedate Date/Time *** Access reserved
'excused Yes/No (boolean)
'fmla Yes/No (boolean)
'A_note String *** JET reserved
'Thetime Date/Time *** Access reserved
'type2 String *** ??? this is the 2nd type field
'initial String


***NOTE: I changed some field names


I created an unbound form and added a button and 11 unbound controls (9 text
boxes and 2 checkboxes).
These are the control names:

FromDate
thrudate
X_Type
Sssn
TheName
realtime
TheDate
Excused
fmla
A_Note
TheTime
Initial


In the click event of the button, I put

"FoxPro2Access"



then I pasted in the following code:

(watch for line wrap!!!)


'---------------------------------------------------

'http://allenbrowne.com/AppIssueBadWord.html
Public Sub FoxPro2Access()
On Error GoTo Err_FoxPro2Access

Dim d As DAO.Database
Dim strSQL As String
Dim TheDate As Date
Dim diff As Integer
Dim Kount As Integer
Dim CurrentID As Long
'Public diff, count ** FoxPro

Set d = CurrentDb
'm.thrudate = m.thrudate + 1
'diff = (m.thrudate - m.Date)
'Me.FromDate and Me.ThruDate are controls on the form
diff = DateDiff("d", Me.FromDate, Me.thrudate) + 1
CurrentID = DMax("ID", "Except")
TheDate = Me.FromDate
'For count = 1 To diff
For Kount = 1 To diff
'm.id = except.id + 1
'inc ID
CurrentID = CurrentID + 1

'INSERT INTO except
'(id,type,ssn,name,realtime,date,excused,fmla,note,time,type,initial);
'VALUES

'(m.id,m.type,m.ssn,m.name,m.realtime,m.date,m.excused,m.fmla,m.note,m.time,m.type,m.initial)

'create the SQL string
'this assumes these data types:
'field datatype
'------------------
'id Long
'Type1 String *** Access reserved
'ssn String
'TheName String *** Access reserved
'realtime Date/Time
'Thedate Date/Time *** Access reserved
'excused Yes/No (boolean)
'fmla Yes/No (boolean)
'A_note String *** JET reserved
'Thetime Date/Time *** Access reserved
'type2 String *** ??? this is the 2nd type field
'initial String

strSQL = "INSERT INTO except
(id,type1,ssn,TheName,realtime,Thedate,excused,fmla,A_note,Thetime,type2,initial)"
strSQL = strSQL & " VALUES ("
'*** this is where you set where th values come from ***
strSQL = strSQL & CurrentID & ", '" & Me.X_Type & "', '" & Me.Sssn & "',
'"
strSQL = strSQL & Me.TheName & "', #" & Me.realtime & "#, #" & TheDate &
"#, "
strSQL = strSQL & Me.Excused & ", " & Me.fmla & ", '" & Me.A_Note & "', #"
strSQL = strSQL & Me.TheTime & "#, '" & Me.X_Type & "', '" & Me.Initial
& "');"

'Debug.Print strSQL
d.Execute strSQL, dbFailOnError

' IF m.day=1
' =messagebox("First multiple day record was entered",0,"Record
Entered")
' Else
' =messagebox("Next multiple day record was entered",0,"Record
Entered")
' End If

'you might want to use a text box on the form
'instead of the message box
If Kount = 1 Then
MsgBox "First multiple day record was entered", 0, "Record Entered"
Else
MsgBox "Next multiple day record was entered", 0, "Record Entered"
End If

'm.Date = m.Date + 1
'incs the Date
TheDate = DateAdd("d", 1, TheDate)
'ENDFOR
Next

Exit_FoxPro2Access:
Set d = Nothing
Exit Sub

Err_FoxPro2Access:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_FoxPro2Access

End Sub
'--------------------------------------------------


HTH