Adding records to a table

[JezzaHyde]

Hi there folks,
I seem to have backed myself into a corner and am looking for the least painful way out of it.
Here's some background info to my dilemma: I am trying to compile a central repository of client information. So far i have successfully sourced all the various pieces of data and have them sitting nicely in a table. Each record is automatically allocated a Replication ID as the identifier. As a Rep ID is not very pretty to produce in reports, i created a function that basically concatenated the first 3 letters of the Name with the last 3 digits of the Rep ID, to produce a code that is easier for the user to comprehend.
Here's my problem...i want to be able to add records to the table. The problem is that I want add the details straight into the table, along with the code generated by my expression. At the moment i am storing the saved details into a table, then running a query to create the new code, and then appending this to the main table.
Is there anyway of creating this "mini-code" on the fly so to speak, so that i can add details straight into the main table??

Thanks

-Jezza

P.S Sorry about the long winded question!!

 

[FoxProProgrammer]

Hi Jezza,

The first question that I would have is why you want to store a calculated field in the table. It isn't needed because you can create the calculated field on the fly when the report is created. That said, if you want to store the calculated field, try the following. This example assumes that you are adding new records through a Form, that the name of the calculated field is "code", and that RepID is a number. Add this code to the Form's After Update event.

Dim rs as Recordset

Set rs = Me.RecordsetClone

rs.Edit
rs!code = Left(Name, 3) & Right(Trim(Str(RepID)), 3)
rs.Update

rs.Close

If this code doesn't work, please provide more details and I'll try to help further.

Best regards,
dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

By the way, another way to do this is to create a bound text box on the form. You could assign the value of the "code" text box in the After Update event of the Name text box. You could hide the text box if desired.

Place this code in the After Udpate event of the Name text box.

code = Left(Name, 3) & Right(Trim(Str(RepID)), 3)

This example assumes that your form is bound.
dz
dzaccess@yahoo.com

 

[JezzaHyde]

No luck on either of those dz, thanks for trying though.

The first method can't recognise the

 rs.Edit

line...and the second method is just frustrating. Even though the ID appears in the RepID text box, when i try to display the final 3 digits it just gives me "???"

As for why i want to display it; i was hoping to be able to search through records based on that generated code.

Cheers

-Jezza

 

[273485]

Dear all,

what is bound meaning ?

thnks in advance.

 

[JezzaHyde]

Essentially, bound refers to the field that a control is associated with.

If a control is bound, it is linked with a field in an underlying table or query. If you add or modify the details within the form, the control containing the updated values will subsequently update the record in the underlying table.
Similarly, a control will display the values stored in a field in a table/query if you are using the form to view data.

Hope that clears it up

-Jezza

 

[FoxProProgrammer]

Hi Jezza,

If you post more information about your form, I'll try to help you figure out what's wrong. Is the Record Source a table or a query? If it's a query, can you post it. Are the fields on the form bound to the table or query? The rs.Edit syntax should work if it accepted the Set rs = Me.RecordsetClone without an error. Did you run it in debug mode and step through the code to see what's happening? What is the error?

The second method should work as well, but I don't know what you mean by "the ID appears in the RepID text box, when i try to display the final 3 digits it just gives me "???". If you post your code I'll see if I can see what is causing that.

By the way, you can search for a record based on a calculated field without storing it in the table. Here's an example:

Dim rs As Recordset

Set rs = Me.RecordsetClone

rs.FindFirst "Left(testname, 3) = 'DEF' And Right(RepID, 3) = 3"

If Not rs.NoMatch Then
MsgBox "Match found"
Else
MsgBox "No match found"
End If

It is generally not recommended to store calculated fields in a table, but obviously there are times when one might want to do it.

Best, dz
dzaccess@yahoo.com

 

[JezzaHyde]

Hey dz,
The following is the code i wrote to extract the 3 letters from the client name.

Public Function CheckSpace(strMy_String As String) As String

' The purpose of this function is to accept a string - the Name of the
' client - and return the first 3 letter segment

Dim i As Integer
Dim strSeg As String        'Temp store of string segment
Dim strCode As String       'Contains successful string segment
Dim blnSpace As Boolean
Dim length As Integer

Dim char1 As String
Dim char2 As String
Dim char3 As String

length = Len(strMy_String)
blnSpace = True

'Loop through the string and search for the first 3 letter sequence that
'does not contain a space

For i = 1 To length
    If (blnSpace = True) Then
        strSeg = Mid(strMy_String, i, 3)
        char1 = Mid(strSeg, 1, 1)
        char2 = Mid(strSeg, 2, 1)
        char3 = Mid(strSeg, 3, 1)
        
        If (char1 = " " Or char2 = " " Or char3 = " ") Then
            blnSpace = True
        Else
            blnSpace = False
            strCode = strSeg
        End If
        
    ElseIf (length < 3) Then
        strCode = strSeg
        blnSpace = False
        
    End If
   
Next i

CheckSpace = strCode

End Function

In a query, i create an expression that concantenates the result of this function with an "-" and the final 3 characters of the RepID, as such:

Expr1 = CheckSpace([CLIENT]![CLIENT NAME]) & "-" & Mid([CLIENT]![CLIENT ID], 35, 3)

Now this worked fine when applied to all the data i had imported in, but now that i want to add records via a form, i can't find a way to create this code in one step. Instead i'm having to bound the form to a temporary table, run the query on this table, and then append the record to the base table.

I'll give that latest idea, about searching for an expression without it being stored in the table, a go. That looks like a pretty sound theory.

Thanks

-Jezza

 

[FoxProProgrammer]

Jezza,

I think that you would save yourself some hassle if you search for the calculated field without storing it in the table. If you decide to store the data in the table, I'll try to help you find the problem. For now I just wanted to give you a suggestion to replace your CheckSpace function. I realize that there are many ways to write a program, and I sincerely hope that you don't get offended at this suggestion. Your code loops until i = length, so it continues to loop even after it finds the first three characters that aren't blank. The following code stops as soon as it finds what you are looking for.

Public Function CheckSpace(strMy_String As String) As String

Dim i As Integer
Dim strSeg As String 'Temp store of string segment
Dim blnSpace As Boolean

i = 1

If Len(strMy_String) < 3 Then
CheckSpace = strSeg
blnSpace = False
Else
blnSpace = True
End If

'Loop through the string and search for the first 3 letter sequence that
'does not contain a space

Do While blnSpace
strSeg = Mid(strMy_String, i, 3)
If InStr(1, strSeg, &quot; &quot > 0 Then
blnSpace = True
Else
blnSpace = False
CheckSpace = strSeg
End If
i = i + 1
Loop

End Function
dz
dzaccess@yahoo.com

 

[JezzaHyde]

Hey dz,
Thanks for that, i'd sort of whipped up that code rather quickly and hadn't noticed that fact yet...cheers.

I also took your advice and i am now creating the expression within the form and searching via it from there. The only problem with this is that it is giving me major speed problems. When i enter a search string in the combo box, it takes quite a bit of time narrow down the search and provide me with options. Any ideas??

Cheers

-Jezza

 

[FoxProProgrammer]

Hey Jezza,

This is the first time that you mentioned a combo box. What is it for? Are you running a query in the After Update event of the combo box? Please post your code. How many records are in your table? A query should run fairly fast. You might try creating an index on the calculated field to see if the speed improves. The calculated field should be unique since the RepID is unique, right? dz
dzaccess@yahoo.com

 

[JezzaHyde]

dz,
Yeah...sorry about that mate. As it stands at the moment, my form contains a list box, a combo box (both unbound) and a series of text boxes required for viewing the selected record.
At the moment, the user selects a category from the list box, which is then used in turn as the search criteria in the combo box. Basically what happens here is that depending on what the user selects as the search criteria, the rowsource of the combo box is manipulated, so that the Client Name is always the bound column.
I have no troubles with this process and everything operates smoothly, until i elect to search by CLIENT CODE, the expression that is evaluated within the form. At this point, the search process slows right down, and i can't seem to work out why.

At the moment there are a tad over 2000 records, and yes, the Client Code is unique in every instance.

Thanks for all the help and i hope these clears things up a bit

-Jezza

 

[FoxProProgrammer]

Hi Jezza,

A table with 2000 records is small and the query should not take long. If you want to zip the database and email it to me, I'll look at it. I can't think of any reason based on what you have said that would cause the query to be slow. dz
dzaccess@yahoo.com