Finding a value and then finding a value from that

[andycambo via AccessMonster.com]

Hi,

I'm currently trying to get automatic numbering process created. I'm trying
to use the ELookup function to help with this but I'm not too sure if I have
got it right.

What I am trying to do is trying to find fields with the same number (that is
inputted by the user) and then from the findings find the next number to be
used (they run in order.

For example. The user inputs 060809 (which will get stored in tblMatters
under mDateOfContact). The ELookup function should then find all the fields
with the same date as what the user has inputted. It then checks what unique
numbers are attatched to this records, and then finds the highest number
(mUniqueNumber). If the same mDateOfContact is used then the mUniqueMatter
must be increased by one, if it hasn't been used before then mUniqueMatter
should equal 1.

Here is my code so far.

Private Sub Command35_Click()

Dim intHighestNumber As Integer
Dim dateOfContact As Long
Dim strSQL As String

'store user input for Date Of Contact
dateOfContact = InputBox("Please Enter the First Date of Contact (000000)")

'Finds the next highest unique matters number and adds 1

intHighestNumber = ELookup("[mUniqueMatter]", " tblMatters", "[mDateOfContact]
", "mDateOfContact DESC")

intHighestNumber = intHighestNumber + 1

'Inserts the above findings into the fields in tblMatters
strSQL = "INSERT INTO tblMatters([mDateOfContact],[mUniqueMatter])" & _
"VALUES (dateOfContact, intHighestNumber);"

End Sub

Any help would be much appreciated.

Thanks
Andy.

 

[Steve Sanford]

If the field "mDateOfContact" in the table "tblMatters" is a text field, then
this should work:

'-------------------------------------------
Private Sub Command35_Click()

Dim intHighestNumber As Integer
Dim dateOfContact As String
Dim strSql As String

'store user input for Date Of Contact
dateOfContact = InputBox("Please Enter the First Date of Contact (000000)")

'Finds the next highest unique matters number and adds 1
' need to use the NZ() function
intHighestNumber = Nz(ELookup("[mUniqueMatter]", " tblMatters", _
"[mDateOfContact] = '" & dateOfContact & "'", "mDateOfContact DESC"), 0)

intHighestNumber = intHighestNumber + 1

'Inserts the above findings into the fields in tblMatters
strSql = "INSERT INTO tblMatters(mDateOfContact, mUniqueMatter)" & _
" VALUES ('" & dateOfContact & "', " & intHighestNumber & ");"

CurrentDb.Execute strSql, dbFailOnError

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


HTH

 

[andycambo via AccessMonster.com]

If the field "mDateOfContact" in the table "tblMatters" is a text field, then
this should work:

Thanks for your help. I got it working with this code, pretty much what you
had put.

intHighestNumber = CInt(Nz(ELookup("[mUniqueMatter]", _
"tblMatters", _
"[mDateOfContact]= '" & dateOfContact &
"'", "[mUniqueMatter] DESC"), 0))

intHighestNumber = intHighestNumber + 1

Another question though.

I'm trying to put together the dateOfContact and intHighestNumber into a
string but each value separated by a /.

I keep getting run time error when I try my code. I've tried all sorts of
ideas but nothing has worked.

Here's what I am trying to use.

ufnString = dateOfContact & "/" & intHighestNumber

I get a type mis-match run time error when using this. I think it's because
it is trying to divide the two values which can not happen. How can I use
'/' in a string?

Thanks
Andy.

 

[Steve Sanford]

Glad you got it working. You shouldn't need to use the CInt() function; the
NZ() function takes care of that by converting a null to, in this case, a
zero.

You should add some checks for length. What gets entered if someone enters
"60909" or "06092009"?


About your other question, did you try

ufnString = dateOfContact & "/" & CStr(intHighestNumber)


Of course, "ufnString" needs to be declared a string or, if a field in a
table, then defined as a type Text.

Also, where is this being used? In the sub "Command35_Click()"?

---------
FWIW, you should take the time to rename objects. Access does a poor job....
it is easier to know what a piece of code does if it is named
"cmdOpenFile_Click()" or "btnProcessPayment_Click()" than
"Command35_Click()". Especially in six months when you are modifying the
code....I kept saying "*What* was I thinking???"
Then I started renaming everything. <g>
---------

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

If the field "mDateOfContact" in the table "tblMatters" is a text field, then
this should work:

Thanks for your help. I got it working with this code, pretty much what you
had put.

intHighestNumber = CInt(Nz(ELookup("[mUniqueMatter]", _
"tblMatters", _
"[mDateOfContact]= '" & dateOfContact &
"'", "[mUniqueMatter] DESC"), 0))

intHighestNumber = intHighestNumber + 1

Another question though.

I'm trying to put together the dateOfContact and intHighestNumber into a
string but each value separated by a /.

I keep getting run time error when I try my code. I've tried all sorts of
ideas but nothing has worked.

Here's what I am trying to use.

ufnString = dateOfContact & "/" & intHighestNumber

I get a type mis-match run time error when using this. I think it's because
it is trying to divide the two values which can not happen. How can I use
'/' in a string?

Thanks
Andy.

 

[andycambo via AccessMonster.com]

You should add some checks for length. What gets entered if someone enters
"60909" or "06092009"?

That's a good idea. I'll have a look around to see how to achieve that.

I tried something very similar to - ufnString = dateOfContact & "/" & CStr
(intHighestNumber). I've just tried what you suggested though and I was
still getting the same problem.

Yes, I just haven't renamed it from its default as I'm still trying to get it
all coded first. This is how it will be used and what I'm working towards.

The user is viewing a client's information page, they then can click this
button to create a new matter (new case/file). They are asked for the first
date of contact. The date entered is then looked up and given a unique
matter, these are then joined together to create a UniqueFileNumber. I also
have to get the Client ID from the client table they were viewing (the one
they clicked the button on) and also this ID put in the same record as the
above values (this how the tables are linked). I haven't got to that bit yet
and I'm not quite sure how to get started on it so any pointers are more than
welcome.

The table amongst other fields will look a little like this (hopefully)

mUniqueMatter mDateOfContact mUFN mClientID

1 - 121208 - 1212082/001 - 1
2 - 121208 - 121208/002 - 15
1 - 150109 - 150109/001 -
32
3 - 121208 - 121208/003 - 15
..............

10 - 121208 - 121208/010 -
12

The - are just to show different fields.

Andy.

 

[andycambo via AccessMonster.com]

I've changed the names of the buttons as you suggested, makes more sense now.

If anyone else could help me on the other problems, please do!

Thanks
Andy.

 

[Steve Sanford]

If your client's information page (form) is bound to the client table (or
query), then the ClientID value is avaliable. Put a text box bound to the
field in the detail section of the form, setting the visible property to NO.
The ClientID will then be available.



ufnString = dateOfContact & "/" & Format(CStr(intHighestNumber), "000")

In my testing of the above snippet of code, I didn't have any errors.
Here is the Sub again with modifications:

'------------------------------------
Private Sub btnNewCase_Click()

Dim intHighestNumber As Integer
Dim dateOfContact As String
Dim strSql As String
Dim ufnString As String
Dim ClientIDFK As Long

'store user input for Date Of Contact
dateOfContact = InputBox("Please Enter the First Date of Contact (000000)")

'-----------------------------------------
'alternate way of getting the date - read the date from a control on the form
' the control could have an input mask of "00/00/0000"
' (dd/mm/yyyy format)
'then use Day(), Month() & Year() date function to create the string
'use Format() to ensure day & month two chars

' dateOfContact = Format(CStr(Day(Me.dteFirstDate)), "00")
' dateOfContact = dateOfContact & Format(CStr(Month(Me.dteFirstDate)), "00")
' dateOfContact = dateOfContact & Right(CStr(Year(Me.dteFirstDate)), 2)
'-----------------------------------------


'Finds the next highest unique matters number and adds 1
' need to use the NZ() function
intHighestNumber = Nz(ELookup("[mUniqueMatter]", " tblMatters", _
"[mDateOfContact] = '" & dateOfContact & "'", "mDateOfContact DESC"), 0)

intHighestNumber = intHighestNumber + 1

ufnString = dateOfContact & "/" & Format(CStr(intHighestNumber), "000")

'get client ID from form
ClientIDFK = Me.mClientID


'Inserts the above findings into the fields in tblMatters
strSql = "INSERT INTO tblMatters(mDateOfContact, mUniqueMatter,
mClientID)" & _
" VALUES ('" & dateOfContact & "', " & intHighestNumber & ", " &
ClientIDFK & ");"

CurrentDb.Execute strSql, dbFailOnError

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


One of the things I tried was to have a text box on a form (with an input
mask) to get the date of first contact. Then use Date functions to get a
string for "mDateOfContact".

By entering an actual date, it is self limiting to valid dates.


HTH

 

[andycambo via AccessMonster.com]

Steve Sanford wrote:

'alternate way of getting the date - read the date from a control on the form
' the control could have an input mask of "00/00/0000"
' (dd/mm/yyyy format)
'then use Day(), Month() & Year() date function to create the string
'use Format() to ensure day & month two chars

' dateOfContact = Format(CStr(Day(Me.dteFirstDate)), "00")
' dateOfContact = dateOfContact & Format(CStr(Month(Me.dteFirstDate)), "00")

' dateOfContact = dateOfContact & Right(CStr(Year(Me.dteFirstDate)), 2)


Thank-you so, so much for your reply. You don't know how much this is
helping me along. I'm new to programming Access (not programming though) and
I've been thrown into the deep end by my employer, so I'm trying to learn as
quickly as possible and people like yourself are a credit to this community!

I'm not totally sure about what you've mentioned (above). I think I
understand what you mean... do you mean automatically take the date from a
form and then use that? Or do you mean something else? The reason I'm
asking the user to input the date of contact is beacause a matter may not
actually be opened at the first date of contact, it may be several days after
the event so I'm not able to use the current date.

The Client's ID is automatically being inserted into the field now so thank-
you for that. I'm still get the error on the ufnString though! I will keep
working on that though, must be something within my table design.

I've just come across the use of the RIGHT & LEFT functions. Is it possible
to take the first 5 letters of the clients surname? Asking this because my
goal is to eliminate user input as much as possible, hence the code.

At the moment (on the database currently in use) the user has to input, the
Client's first 5 letters of their surname, their first name initial, the date
of contact and the unique number to create a File Number that looks like this.


Client John Smith - File Number: SMITH/J/070809/001.

Broken down as - First 5 letters of Surname/First Name Initial / Date Of
Contact / Unique Matter

However if I could automatically take every piece of information without any
user input (apart from the date of contact) then that would be great.

Is something like that possible or will it be easier for the user to just
type in the whole File Number?

Thank again for your help,
Andy.

 

[andycambo via AccessMonster.com]

Just thinking, would something like below get the first 5 letters of the
surname?

Dim Surname As String
Dim partSurname As String

Surname = me.cSurname

partSurname = "LEFT((Surname), 5)"

Thanks
Andy.

 

[Steve Sanford]

I don't know your MDB structure, so I'll design a form as an example.

There is a table named "tblClientInfo". It has fields:

name type
--------------------------------
ClientID Long
Surname Text
F_Name Text


There is a query named "qryClientInfo":

SELECT tblClientInfo.ClientID, tblClientInfo.Surname, tblClientInfo.F_Name
FROM tblClientInfo
ORDER BY tblClientInfo.Surname, tblClientInfo.F_Name;


There is a form named "frmClientInfo" that is in Single form view. The form
is bound to the query "qryClientInfo". (the record source for the form is a
query)

In the detail section of the form are three text boxes that are bound to the
three fields of the form's record source:

- One text box is named "tbClientID". The control source is "ClientID". The
visible property for the client id text box is set to NO.
- The second text box is named "tbSurname". The control source is "Surname".
- And the third text box is named "tbF_Name" , with a control source of
"tbF_Name".


In the header of the form is an *unbound* text box, named "dteContactDate".
On the FORMAT tab, the format property is set to "Short Date". On the DATA
tab, the Input Mask property is set to "00/00/0000;0;_" (no quotes).

Below the unbound text box is a button "btnNewCase". The code for the button
click event is:

'### untested code #####

'------------------------------------
Private Sub btnNewCase_Click()

Dim intHighestNumber As Integer
Dim dateOfContact As String
Dim strSql As String
Dim ufnString As String
Dim ClientIDFK As Long

' check if date was entered
If Len(Trim(Me.dteContactDate)) = 0 Then
MsgBox "No Date Entered! Please enter a date to proceed...."
Exit Sub
End If

'-----------------------------------------
' read the date from a control on the form
' the control could have an input mask of "00/00/0000"
' (dd/mm/yyyy format)
'then use Day(), Month() & Year() date function to create the string
'use Format() to ensure day & month two chars

dateOfContact = Format(CStr(Day(Me.dteContactDate)), "00")
dateOfContact = dateOfContact & Format(CStr(Month(Me.dteContactDate)),
"00")
dateOfContact = dateOfContact & Right(CStr(Year(Me.dteContactDate)), 2)
'-----------------------------------------

'Finds the next highest unique matters number and adds 1
' need to use the NZ() function
intHighestNumber = Nz(ELookup("[mUniqueMatter]", " tblMatters", _
"[mDateOfContact] = '" & dateOfContact & "'", "mDateOfContact DESC"), 0)

intHighestNumber = intHighestNumber + 1

'get client ID from form
ClientIDFK = Me.tbClientID

'create the File Number
'File Number format example: SMITH/J/070809/001
ufnString = Left(Me.tbSurname, 5) & "/" & Left(Me.tbF_Name, 1) & "/"
ufnString = ufnString & dateOfContact & "/"
ufnString = ufnString & Format(CStr(intHighestNumber), "000")

'create the SQL insert string
'
strSql = "INSERT INTO tblMatters"
strSql = strSql & "(mDateOfContact, mUniqueMatter, mClientID, mUFN)"
strSql = strSql & " VALUES ('" & dateOfContact & "', "
strSql = strSql & intHighestNumber & ", " & ClientIDFK & ", "
strSql = strSql & Chr(34) & ufnString & Chr(34) & ");"

'Debug.Print strSql

'Inserts the above findings into the fields in tblMatters
CurrentDb.Execute strSql, dbFailOnError

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



Note that this code is modified and will not work with your existing form.

HTH

 

[Steve Sanford]

No. Don't use quotes around the Left() function. The extra parens don't hurt,
but aren't necessary.


Dim Surname As String
Dim partSurname As String

Surname = me.cSurname

partSurname = LEFT(Surname, 5)



--- Or ---


Dim partSurname As String

partSurname = LEFT(me.cSurname, 5)


HTH

 

[Steve Sanford]

Oops, shoud have been

name type
--------------------------------
ClientID Long (PK)
Surname Text
F_Name Text


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I don't know your MDB structure, so I'll design a form as an example.

There is a table named "tblClientInfo". It has fields:

name type
--------------------------------
ClientID Long
Surname Text
F_Name Text


There is a query named "qryClientInfo":

SELECT tblClientInfo.ClientID, tblClientInfo.Surname, tblClientInfo.F_Name
FROM tblClientInfo
ORDER BY tblClientInfo.Surname, tblClientInfo.F_Name;


There is a form named "frmClientInfo" that is in Single form view. The form
is bound to the query "qryClientInfo". (the record source for the form is a
query)

In the detail section of the form are three text boxes that are bound to the
three fields of the form's record source:

- One text box is named "tbClientID". The control source is "ClientID". The
visible property for the client id text box is set to NO.
- The second text box is named "tbSurname". The control source is "Surname".
- And the third text box is named "tbF_Name" , with a control source of
"tbF_Name".


In the header of the form is an *unbound* text box, named "dteContactDate".
On the FORMAT tab, the format property is set to "Short Date". On the DATA
tab, the Input Mask property is set to "00/00/0000;0;_" (no quotes).

Below the unbound text box is a button "btnNewCase". The code for the button
click event is:

'### untested code #####

'------------------------------------
Private Sub btnNewCase_Click()

Dim intHighestNumber As Integer
Dim dateOfContact As String
Dim strSql As String
Dim ufnString As String
Dim ClientIDFK As Long

' check if date was entered
If Len(Trim(Me.dteContactDate)) = 0 Then
MsgBox "No Date Entered! Please enter a date to proceed...."
Exit Sub
End If

'-----------------------------------------
' read the date from a control on the form
' the control could have an input mask of "00/00/0000"
' (dd/mm/yyyy format)
'then use Day(), Month() & Year() date function to create the string
'use Format() to ensure day & month two chars

dateOfContact = Format(CStr(Day(Me.dteContactDate)), "00")
dateOfContact = dateOfContact & Format(CStr(Month(Me.dteContactDate)),
"00")
dateOfContact = dateOfContact & Right(CStr(Year(Me.dteContactDate)), 2)
'-----------------------------------------

'Finds the next highest unique matters number and adds 1
' need to use the NZ() function
intHighestNumber = Nz(ELookup("[mUniqueMatter]", " tblMatters", _
"[mDateOfContact] = '" & dateOfContact & "'", "mDateOfContact DESC"), 0)

intHighestNumber = intHighestNumber + 1

'get client ID from form
ClientIDFK = Me.tbClientID

'create the File Number
'File Number format example: SMITH/J/070809/001
ufnString = Left(Me.tbSurname, 5) & "/" & Left(Me.tbF_Name, 1) & "/"
ufnString = ufnString & dateOfContact & "/"
ufnString = ufnString & Format(CStr(intHighestNumber), "000")

'create the SQL insert string
'
strSql = "INSERT INTO tblMatters"
strSql = strSql & "(mDateOfContact, mUniqueMatter, mClientID, mUFN)"
strSql = strSql & " VALUES ('" & dateOfContact & "', "
strSql = strSql & intHighestNumber & ", " & ClientIDFK & ", "
strSql = strSql & Chr(34) & ufnString & Chr(34) & ");"

'Debug.Print strSql

'Inserts the above findings into the fields in tblMatters
CurrentDb.Execute strSql, dbFailOnError

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



Note that this code is modified and will not work with your existing form.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Steve Sanford wrote:

'alternate way of getting the date - read the date from a control on the form
' the control could have an input mask of "00/00/0000"
' (dd/mm/yyyy format)
'then use Day(), Month() & Year() date function to create the string
'use Format() to ensure day & month two chars

' dateOfContact = Format(CStr(Day(Me.dteFirstDate)), "00")
' dateOfContact = dateOfContact & Format(CStr(Month(Me.dteFirstDate)), "00")

' dateOfContact = dateOfContact & Right(CStr(Year(Me.dteFirstDate)), 2)


Thank-you so, so much for your reply. You don't know how much this is
helping me along. I'm new to programming Access (not programming though) and
I've been thrown into the deep end by my employer, so I'm trying to learn as
quickly as possible and people like yourself are a credit to this community!

I'm not totally sure about what you've mentioned (above). I think I
understand what you mean... do you mean automatically take the date from a
form and then use that? Or do you mean something else? The reason I'm
asking the user to input the date of contact is beacause a matter may not
actually be opened at the first date of contact, it may be several days after
the event so I'm not able to use the current date.

The Client's ID is automatically being inserted into the field now so thank-
you for that. I'm still get the error on the ufnString though! I will keep
working on that though, must be something within my table design.

I've just come across the use of the RIGHT & LEFT functions. Is it possible
to take the first 5 letters of the clients surname? Asking this because my
goal is to eliminate user input as much as possible, hence the code.

At the moment (on the database currently in use) the user has to input, the
Client's first 5 letters of their surname, their first name initial, the date
of contact and the unique number to create a File Number that looks like this.


Client John Smith - File Number: SMITH/J/070809/001.

Broken down as - First 5 letters of Surname/First Name Initial / Date Of
Contact / Unique Matter

However if I could automatically take every piece of information without any
user input (apart from the date of contact) then that would be great.

Is something like that possible or will it be easier for the user to just
type in the whole File Number?

Thank again for your help,
Andy.

 

[andycambo via AccessMonster.com]

Thanks for your reply again. I've given what you have written ago but I have
been unable to get it working.

The way I was the form was before I tried to implement the above I had the
record source as a query called Client Extended. This put the first and last
names together to create the client's name and through this the user could
choose the client. I've posted a link to the form I have created so you can
see what I am trying to achieve.

This is the client extended code;

SELECT IIf(IsNull([cLastName]),IIf(IsNull([cFirstName]),[cFirstName]),IIf
(IsNull([cFirstName]),[cLastName],[cLastName] & ", " & [cFirstName])) AS
[File As], IIf(IsNull([cLastName]),IIf(IsNull([cFirstName]),[cFirstName]),IIf
(IsNull([cFirstName]),[cLastName],[cFirstName] & " " & [cLastName])) AS
ClientName, tblClientInfo.*
FROM tblClientInfo
ORDER BY IIf(IsNull([cLastName]),IIf(IsNull([cFirstName]),[cFirstName]),IIf
(IsNull([cFirstName]),[cLastName],[cLastName] & ", " & [cFirstName])), IIf
(IsNull([cLastName]),IIf(IsNull([cFirstName]),[cFirstName]),IIf(IsNull(
[cFirstName]),[cLastName],[cFirstName] & " " & [cLastName]));

Form Design

http://i630.photobucket.com/albums/uu29/andycambo/access_form.jpg

http://i630.photobucket.com/albums/uu29/andycambo/access_form_design.jpg


I've created the query as you suggested but two problems appeard by doing
this. Once I choose the form control source as the query everytime I try to
open the form I get a "Enter Parameter Value" box appear saying -
"tblClientInfo.LastName" and then a box to enter details. I'm not sure why
this is happening.

Also, because my forms control source was the Client's Extended query I was
able to choose the clients by using the drop down box. But I am unable to do
this now, is it possible to add to the query you've created so I am still
able to do this?

Thanks
Andy.

 

[Steve Sanford]

OK, lets drop back a little.

*Be sure to try this on a copy of your database.*

Move the "Create New Matter" button down towards the bottom of the form.
Above it, add an unbound text box.

Set these properties:

Name: dteContactDate
Format: Short Date
Input Mask: 00/00/0000


In the code for the button, I commented out the execute command. I added a
message box to display the SQL string.

I think all of the variables and control/fields are correct in the code.

Change the code for the button click to:

'------------------------------------
Private Sub btnNewCase_Click()

Dim intHighestNumber As Integer
Dim dateOfContact As String
Dim strSql As String
Dim ufnString As String
Dim ClientIDFK As Long

' check if date was entered
If Len(Trim(Me.dteContactDate)) = 0 Then
MsgBox "No Date Entered! Please enter a date to proceed...."
Exit Sub
End If

'-----------------------------------------
' read the date from a control on the form
' the control could have an input mask of "00/00/0000"
' (dd/mm/yyyy format)
'then use Day(), Month() & Year() date function to create the string
'use Format() to ensure day & month two chars

dateOfContact = Format(CStr(Day(Me.dteContactDate)), "00")
dateOfContact = dateOfContact & Format(CStr(Month(Me.dteContactDate)),
"00")
dateOfContact = dateOfContact & Right(CStr(Year(Me.dteContactDate)), 2)
'-----------------------------------------

'Finds the next highest unique matters number and adds 1
' need to use the NZ() function
intHighestNumber = Nz(ELookup("[mUniqueMatter]", " tblMatters", _
"[mDateOfContact] = '" & dateOfContact & "'", "mDateOfContact DESC"), 0)

intHighestNumber = intHighestNumber + 1

'get client ID from form
ClientIDFK = Me.tbClientID

'create the File Number
'File Number format example: SMITH/J/070809/001
ufnString = Left(Me.cLastName, 5) & "/"
ufnString = ufnString & Left(Me.cFirstName, 1) & "/"
ufnString = ufnString & dateOfContact & "/"
ufnString = ufnString & Format(CStr(intHighestNumber), "000")

'create the SQL insert string
'
strSql = "INSERT INTO tblMatters"
strSql = strSql & "(mDateOfContact, mUniqueMatter, mClientID, mUFN)"
strSql = strSql & " VALUES ('" & dateOfContact & "', "
strSql = strSql & intHighestNumber & ", " & ClientIDFK & ", "
strSql = strSql & Chr(34) & ufnString & Chr(34) & ");"

'-----for debugging-----
' delete or comment out when done debugging
Msgbox strSQL
'Debug.Print strSql
'-----for debugging-----

'Inserts the above findings into the fields in tblMatters
' uncomment the next line when debugging is complete

'CurrentDb.Execute strSql, dbFailOnError

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


If the SQL string doesn't look right, try setting a breakpoint and stepping
thru the code one line at a time, checking the variables



HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Thanks for your reply again. I've given what you have written ago but I have
been unable to get it working.

The way I was the form was before I tried to implement the above I had the
record source as a query called Client Extended. This put the first and last
names together to create the client's name and through this the user could
choose the client. I've posted a link to the form I have created so you can
see what I am trying to achieve.

This is the client extended code;

SELECT IIf(IsNull([cLastName]),IIf(IsNull([cFirstName]),[cFirstName]),IIf
(IsNull([cFirstName]),[cLastName],[cLastName] & ", " & [cFirstName])) AS
[File As], IIf(IsNull([cLastName]),IIf(IsNull([cFirstName]),[cFirstName]),IIf
(IsNull([cFirstName]),[cLastName],[cFirstName] & " " & [cLastName])) AS
ClientName, tblClientInfo.*
FROM tblClientInfo
ORDER BY IIf(IsNull([cLastName]),IIf(IsNull([cFirstName]),[cFirstName]),IIf
(IsNull([cFirstName]),[cLastName],[cLastName] & ", " & [cFirstName])), IIf
(IsNull([cLastName]),IIf(IsNull([cFirstName]),[cFirstName]),IIf(IsNull(
[cFirstName]),[cLastName],[cFirstName] & " " & [cLastName]));

Form Design

http://i630.photobucket.com/albums/uu29/andycambo/access_form.jpg

http://i630.photobucket.com/albums/uu29/andycambo/access_form_design.jpg


I've created the query as you suggested but two problems appeard by doing
this. Once I choose the form control source as the query everytime I try to
open the form I get a "Enter Parameter Value" box appear saying -
"tblClientInfo.LastName" and then a box to enter details. I'm not sure why
this is happening.

Also, because my forms control source was the Client's Extended query I was
able to choose the clients by using the drop down box. But I am unable to do
this now, is it possible to add to the query you've created so I am still
able to do this?

Thanks
Andy.

 

[andycambo via AccessMonster.com]

OK, lets drop back a little.

*Be sure to try this on a copy of your database.*

Hi,

Okay, I went back to the start again and used your code.

It’s working, I had to make a couple of changes though but one change brought
back an earlier problem.

The first change was ClientIDFK = me.tbClientID. I just changed the last
part to ClientID.

The second change was to ufnString, I’ve changed this to a string called
fileString as what was created was classed as the file Number. However I’ve
tried to create the ufnString (070809/001) and I am getting the problem I had
earlier. The “/†in the ufnString seems to be dividing the numbers for some
reason and giving me answers like. ‘60404.5’ even though on the debug mode it
all looks correct. The mUFN is set as a string in the table design so I am
unsure why it keeps doing this.

Here is my code for UFN string

'Create the UFN
'UFN format example 070809/001
ufnString = dateOfContact & "/"
ufnString = ufnString & Format(CStr(intHighestNumber), "000")

and the change for the INSERT INTO statement

strSQL = "INSERT INTO tblMatters"
strSQL = strSQL & "(mDateOfContact, mUniqueMatter, mClientID, mFileNumber,
mUFN)"
strSQL = strSQL & " VALUES ('" & dateOfContact & "', "
strSQL = strSQL & intHighestNumber & ", " & ClientIDFK & ", "
strSQL = strSQL & Chr(34) & fileString & Chr(34) & ", "
strSQL = strSQL & ufnString & ");"


Thanks for taking me through each step. The debug idea is very helpful and
helped me out nicely. I also really like the idea of the text box instead of
a message box, thanks for that!

Andy.

 

[andycambo via AccessMonster.com]

upperCase = UCase(Me.cLastName)
fileString = Left(upperCase, 5) & "/"

just added the above so that the File Number should read in all capitals such
as SMITH/J/070809/001 instead of Smith/J/070809/001

 

[Steve Sanford]

Good catch. I thought about it, but I have a mind like a sieve.....

You could also use : fileString = Left(UCase(Me.cLastName), 5) & "/"


I also thought about the O people and the space people. Can your filenames
have an apostrophe or a space in it?

O people = O'Malley, O'Brian
space people = Van Buren, Van Helsing, St Pierre

If not, then you would use the Replace() function.

upperCase = UCase(Me.cLastName)
upperCase = Replace(upperCase, "'", "") ' << " ' "
upperCase = Replace(upperCase, " ", "") ' << " "
fileString = Left(upperCase, 5) & "/"

And what about the hyphen people??? (Ty-Meup, Tu-Long)


HTH

 

[Steve Sanford]

Remember,......strings *must* be delimited with single or double quotes. And
dates must be delimited with hashes (#).

Sotry this:

strSQL = "INSERT INTO tblMatters"
strSQL = strSQL & "(mDateOfContact, mUniqueMatter, mClientID, mFileNumber,
mUFN)"
strSQL = strSQL & " VALUES ('" & dateOfContact & "', "
strSQL = strSQL & intHighestNumber & ", " & ClientIDFK & ", "
strSQL = strSQL & Chr(34) & fileString & Chr(34) & ", "
strSQL = strSQL & Chr(34) &ufnString & Chr(34)& ");"


' (Chr(34) is ASCII for the double quote)


---- OR this ----

strSQL = "INSERT INTO tblMatters"
strSQL = strSQL & "(mDateOfContact, mUniqueMatter, mClientID, mFileNumber,
mUFN)"
strSQL = strSQL & " VALUES ('" & dateOfContact & "', "
strSQL = strSQL & intHighestNumber & ", " & ClientIDFK & ", "
strSQL = strSQL & Chr(34) & fileString & Chr(34) & ", '"
strSQL = strSQL & ufnString & "');"

Expanded, the last two lines are:

strSQL = strSQL & Chr(34) & fileString & Chr(34) & ", ' "
strSQL = strSQL & ufnString & " ' );"



HTH

 

[andycambo via AccessMonster.com]

Hi,

Sorry I have posted for a few days, been really busy and will continue to be
until next week, With the time I've had I've been able to get the code
button working properly (with the UFN being entered properly).

The code to filter the ' out is a fantastic idea, do I go about removing the -
in the same way?

Thanks
Andy.

 

[Steve Sanford]

Yes.

upperCase = Replace(upperCase, "-", "")


HTH