Replies

If the bound column of the combo box is the column containg the ID you can use something like

me.CID = me.cboCompanyName

in the click event of the CompanyName combo box.

You can also refer directly to the column conating the ID

me.CID = me.cboCompanyName.column(0)

where the “0” is the column index of the ID field.

Hope this helps.

Richard Aheron
raheron@hotmail.com

I have attached a file with one of the rounding functions I use.

Richard Aheron
raheron@hotmail.com

Try changing the statement

MyDate = [BirthDate+] to

MyDate = me![BirthDate+]

Richard Aheron
raheron@hotmail.com

Try mySQL = “INSERT INTO ” & strTempTable & “….

Richard Aheron
raheron@hotmail.com

You need an additional comma.

Try DoCmd.OpenForm “xyz”,,,”[Global Variable] = [Search Key]”.

Richard Aheron
raheron@hotmail.com

Try this code….

It is called like strDollarValue =CheckNumSpelledOut(CStr(NumericField))

Here is the code.

Option Compare Database ‘Use database order for string comparisons

‘ Using CStr to convert incoming numeric values will assure there are
‘ no commas or leading zeros etc etc.
Function CheckNumSpelledOut$ (numin$)
numin$ = LTrim$(RTrim$(numin$))

If InStr(numin$, “.”) = 0 Then
number$ = numin$
Else
number$ = Left$(numin$, InStr(1, numin$, “.”) – 1)
End If

‘
‘ With CStr used to pass numbers, numbers ending in 0 or 00 will
‘truncate the last 0. So, we cannot just take the last 2 positions
‘on the right and take them to be the cents. We have to find the
‘decimal point, first, then extract everything to the right of it
‘and convert it to 2 positions.
‘
‘ cents$ = Right$(numin$, 2)
If InStr(1, numin$, “.”) = 0 Then ‘No decimal point or cents
cents$ = “00”
ElseIf (Len(numin$) – InStr(1, numin$, “.”)) >= 2 Then
cents$ = Mid$(numin$, InStr(1, numin$, “.”) + 1, 2)
Else
cents$ = Mid$(numin$, InStr(1, numin$, “.”) + 1, 1) & “0”
End If

‘Debug.Print “number: “; number$

numlen% = Len(number$)
hunpart$ = “”
milpart$ = “”
thopart$ = “”

Select Case numlen%
Case 0
Case 1 To 3
hunpart$ = Right$(number$, numlen%)
Case 4 To 6
hunpart$ = Right$(number$, 3)
thopart$ = Left$(number$, numlen% – 3)
Case 7 To 9
milpart$ = Left$(number$, numlen% – 6)
thopart$ = Mid$(number$, numlen% – 5, 3)
hunpart$ = Right$(number$, 3)
Case Else
MsgBox “Design error: number too large to convert”
CheckNumSpelledOut = “Bad Input”
Exit Function
End Select

‘Debug.Print “millions: “; milpart$
‘Debug.Print “thousands: “; thopart$
‘Debug.Print “hundreds: “; hunpart$

If hunpart$ = “000” Then
huntext$ = “”
Else
huntext$ = SpellHundred(hunpart$)
End If

If thopart$ = “000” Then
thotext$ = “”
Else
thotext$ = SpellHundred(thopart$)
End If

If milpart$ = “000” Then
miltext$ = “”
Else
miltext$ = SpellHundred(milpart$)
End If

ftext$ = “”
If miltext$ “” Then
If miltext$ “Zero” Then
ftext$ = miltext$ + ” Million ”
End If
End If

If thotext$ “” Then
If thotext$ “Zero” Then
ftext$ = ftext$ + thotext$ + ” Thousand ”
End If
End If

If huntext$ “” Then
ftext$ = ftext$ + huntext$
End If

‘ftext$ = ftext$ + ” Dollars and ” + cents$ + ” Cents”
If ftext$ “” Then
ftext$ = ftext$ + ” and ” + cents$ + “/100 dollars”
Else
ftext$ = “Zero Dollars and ” & cents$ & “/100 dollars”
End If
CheckNumSpelledOut = ftext$

End Function

Private Function SpellHundred$ (numin$)

Dim digs As String
Dim tens As String
Dim huns As String
Dim numlen As Integer

numlen = Len(numin$)
huns = “”
tens = “”
digs = “”

If numlen = 3 Then
Select Case Left$(numin$, 1)
Case “0”: huns = “”
Case “1”: huns = “One Hundred”
Case “2”: huns = “Two Hundred”
Case “3”: huns = “Three Hundred”
Case “4”: huns = “Four Hundred”
Case “5”: huns = “Five Hundred”
Case “6”: huns = “Six Hundred”
Case “7”: huns = “Seven Hundred”
Case “8”: huns = “Eight Hundred”
Case “9”: huns = “Nine Hundred”
End Select

If Right$(numin$, 2) = “00” Then
SpellHundred = huns
Exit Function
End If
End If

If numlen >= 2 Then
Select Case Mid$(numin$, Len(numin$) – 1, 1)
Case “0”: tens = “”
Case “1”: tens = “Ten”
Case “2”: tens = “Twenty”
Case “3”: tens = “Thirty”
Case “4”: tens = “Forty”
Case “5”: tens = “Fifty”
Case “6”: tens = “Sixty”
Case “7”: tens = “Seventy”
Case “8”: tens = “Eighty”
Case “9”: tens = “Ninety”
End Select
End If

If numlen >= 1 Then
Select Case Right$(numin$, 1)
Case “0”, ” “, “”: digs = “”
Case “1”: digs = “One”
Case “2”: digs = “Two”
Case “3”: digs = “Three”
Case “4”: digs = “Four”
Case “5”: digs = “Five”
Case “6”: digs = “Six”
Case “7”: digs = “Seven”
Case “8”: digs = “Eight”
Case “9”: digs = “Nine”
End Select
End If

If Len(numin$) < 2 Then
SpellHundred = digs
Exit Function
End If

Select Case tens
Case "Ten"
Select Case digs
Case "Zero", "", " ": temp$ = "Ten"
Case "One": temp$ = "Eleven"
Case "Two": temp$ = "Twelve"
Case "Three": temp$ = "Thirteen"
Case "Four": temp$ = "Fourteen"
Case "Five": temp$ = "Fifteen"
Case "Six": temp$ = "Sixteen"
Case "Seven": temp$ = "Seventeen"
Case "Eight": temp$ = "Eighteen"
Case "Nine": temp$ = "Nineteen"
End Select
Case Else
If digs = "" Then
temp$ = tens
Else
temp$ = tens + "-" + digs
End If
End Select

Select Case numlen
Case 0
SpellHundred = ""
Case 1
SpellHundred = digs
Case 2
SpellHundred = temp$
Case 3
If huns = "" Then
If tens = "" Then
If digs = "" Then
SpellHundred = ""
Else
SpellHundred = digs
End If
Else
SpellHundred = temp$
End If
Else
If tens = "" Then
If digs = "" Then
SpellHundred = huns
Else
SpellHundred = huns & " " & digs
End If
Else
SpellHundred = huns & " " & temp$
End If
End If
End Select

End Function

Good luck

Richard Aheron
raheron@hotmail.com

In the group footer insert a text box with the recordsource

=Count([somefield])

where “somefield” is the name of the field on the report you wish to count.

To get the grand total in the rpeort footer, copy the text box in the group footer and paste it into the report footer.

Richard Aheron
raheron@hotmail.com

Check out the “Dirty Property” in Access help.

Good luck

Richard Aheron
raheron@hotmail.com

I am using drive letters. I am reading the file from the C: drive of the machine running the code.

The transfer seems to run if a new table is created, but not going into the table build to receive the data.

The error number is 3001.

Thanks for the help

Richard Aheron
raheron@hotmail.com

In the Tools menu look under the Database Utilities. The Linked Table Manager has moved.

The Database Splitter and Switchboard Manager are there also.

Richard Aheron
raheron@hotmail.com

If this code is in the report you can get the data from the report field by using

me.PM.

If the code lives outside the report you can use

reports!ReportName.PM

as long as the report is open at the time.

Good luck

Richard Aheron
raheron@hotmail.com

Are you trying to get data from the table?

If so, you need to create a record set and then interrogate the field from the recordset.

In code use the OpenRecordset method to open the recordset and then you can reference the field using any of the following.

rst(0)
rst(“name”)
rst![name]

where rst is the recordset and name is the name of the field. 0 is the index of the filed in the recordset.

I hope this helps

Richard Aheron
raheron@hotmail.com

With out knowing any more than is in the note.

Add the field to the table. Then use the same code or query that currently updates the table and add the filed there as well. The draw back to this is that any current rows will not be updated with the PM field.

Hope this helps.

Richard Aheron
raheron@hotmail.com

Use the CInt or CLng functions to return the values to integer values then add the two.

CInt([Fld1]) + CInt([Fld2])

Richard Aheron
raheron@hotmail.com

You could use a function that incremented a global variable.

Function IncrementCounter() as integer

gintCounter = gintCounter + 1

End Function

The global variable would be set outside the function and the initial value could be set then as well.

This function could be called from a query that would update the other field.

Hope this helps

Richard Aheron
raheron@hotmail.com