var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Looping through records
Hi there - I'm doing a database that will need to link with a Paradox database. To accomplish this I need to make a certain field in Access.
I basically have a table that contains a persons first name and surname.
I need to create another field called ID Make that takes the first six characters of the first name, adds in a hyphen and then adds in the first character of the surname.
I can do this easily with a query like:
Left([First Name],6) & "-" & Left([Surname],1)
For example the name William Gates would be turned into:
Now here comes the tricky bit, if there is another William Gates I would get another Willia-G reference but at this point I need it to add a '1' at the end so I would get:
If there was a third I would need Willia-G2.... and so on.
What I could do with is some kind of script that looks at each record in the table and then makes the appropriate ID.
I'd appreciate very much any help anyone can give me.
At the moment I just have a test table called tbl_test
Field: First Name
Field: ID Make
Thanks very much -
The easiest way I can think to do this is to create a simple form with 1 textbox for Firstname("txtFirst") and another for Surname ("txtSur"), then a button to add.
Then you can click on the button with the following coding:
Dim db as DAO.Database
Dim rs as Recordset
Dim strID as String
Dim x as Integer
Dim strFirst as String
Dim strSur as String
Dim sql as String
Set db = Currentdb
Set rs = db.OpenRecordset("tbl_test", dbOpenDynaset)
strID = Left(txtFirst.Value,6) & "-" & Left(txtSur.Value,1)
x = 0
Do While Not rs.EOF
If rs![ID Make] = strID Then
x = x + 1
If x > 0 then
strID = StrID & x
strFirst = "'" & txtFirst.value & "'"
strSur = "'" & txtSur.value & "'"
strID = "'" & strID & "'"
sql = CurrentDb.Execute "INSERT INTO tbl_test ([First Name], Surname, [ID Make]) " & _
"Values(" & strFirst & ", " & strSur & "," & strID & ")"
This ofcourse relies upon you using a form. I think this can be modified to fit a table, but I can't seem to get it to work Sorry.
Thanks Phil - much appreciated !!
Lol I wouldn't thank me yet, you haven't tried it yet
I wrote this from memory, so haven't tried it in VB yet. So if you get an error then its probably me.