HouseBot

Attached is a copy of my HB Contact List Export. But being new to the "scripting world," I have some questions. The root program is in access. The script program works great, as long as all the fields in access are filled. What I would like to have the script do is, locate wherever there is a empty field in access and place a NA (or whatever I want) to be placed in the empty field (so the field in not empty) and program.

I also would like to thank all for help I recieved from this forum. As well all the help I recieved from Scott and especially Richard on bringing some light into the scripting world to me. The light is not as small as a pin hole anymore, it is the size of a quarter. LOL.

Eventually I am going to post a recipe list on the forum if I can get through all 10,000 recipes. I am currently at 2,277 and having hand craps. LOL. The recipes will not be in metric. Sorry Richard.

The post does not allow me to post mdb files. So you can PM me and I will forward it to you. Attached below is the script file:

Option Explicit
'On Error Resume Next

Dim rs
Dim dbConn
Dim Action
Dim FileName
Dim szList


FileName = GetPropertyValue( "CONTACTS.PathAndFileName" )
Set dbConn = CreateObject( "ADODB.Connection" )
dbConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName
Set rs = dbConn.Execute( "SELECT FirstName, LastName FROM [CONTACTSList]")

Do
szList = szList & "Name^" & rs("FirstName") & vbTab & rs("FirstName") & (" ") & rs("LastName") & vbLF
rs.MoveNext
Loop Until rs.EOF

Set rs = Nothing

SetPropertyValue "CONTACTS.Name List", szList



'======================================================================================================
'Continuous running script
Do
Sleep 1
Action = GetPropertyValue("CONTACTS.Action")
If Action <> "Waiting" Then
SetPropertyValue "CONTACTS.Action", "Waiting"
Call Handle_CONTACTSAction(Action)
Sleep 100
End If
Loop

' Reset
Set rs = Nothing
Set FileName = Nothing
Set dbConn = Nothing



'======================================================================================================
Sub Handle_CONTACTSAction(Action)
Dim Data

Data = Split(Action, "^")
Select Case Data(0)
Case "Name": Call HandleName(Data(1))
End Select
End Sub


'=======================================================================================================
Sub HandleName(Data)


Set rs = dbConn.Execute("SELECT FirstName, MiddleName, LastName, Nickname, Address, City, State, PostalCode, HomePhone, WorkPhone, MobilePhone, FaxNumber, AlternativePhone, EmailAddress, Notes FROM [CONTACTSList] WHERE FirstName = '" & Data & "'")

SetPropertyValue "CONTACTS.Cont_NMF", rs("FirstName")
SetPropertyValue "CONTACTS.Cont_NMM", rs("MiddleName")
SetPropertyValue "CONTACTS.Cont_NML", rs("LastName")
SetPropertyValue "CONTACTS.Cont_NMN", rs("Nickname")
SetPropertyValue "CONTACTS.Cont_Add", rs("Address")
SetPropertyValue "CONTACTS.Cont_AddCity", rs("City")
SetPropertyValue "CONTACTS.Cont_AddState", rs("State")
SetPropertyValue "CONTACTS.Cont_Zip", rs("PostalCode")
SetPropertyValue "CONTACTS.Cont_PhoneH", rs("HomePhone")
SetPropertyValue "CONTACTS.Cont_PhoneW", rs("WorkPhone")
SetPropertyValue "CONTACTS.Cont_PhoneC", rs("MobilePhone")
SetPropertyValue "CONTACTS.Cont_PhoneF", rs("FaxNumber")
SetPropertyValue "CONTACTS.Cont_PhoneAlt", rs("AlternativePhone")
SetPropertyValue "CONTACTS.Cont_Email", rs("EmailAddress")
SetPropertyValue "CONTACTS.Cont_Notes", rs("Notes")
Set rs = Nothing
End Sub

James D wrote:Attached is a copy of my HB Contact List Export. But being new to the "scripting world," I have some questions. The root program is in access. The script program works great, as long as all the fields in access are filled. What I would like to have the script do is, locate wherever there is a empty field in access and place a NA (or whatever I want) to be placed in the empty field (so the field in not empty) and program.

I would just overcome the fact that empty fields produce an error.
It would probably work already when you take out the single quote in front of the "On Error Resume Next" however that is not the way to go. Since I don't have the mdb, I cannot test what you mean.

I also would like to thank all for help I recieved from this forum. As well all the help I recieved from Scott and especially Richard on bringing some light into the scripting world to me.

Thnx, you are welcome!

Eventually I am going to post a recipe list on the forum if I can get through all 10,000 recipes. The recipes will not be in metric. Sorry Richard.

Why not? While you're at it! (Just kidding of course)

'On Error Resume Next
szList = szList & "Name^" & rs("FirstName") & vbTab & rs("FirstName") & (" ") & rs("LastName") & vbLF

Not sure how (" ") would work with the () around them. I have never tried it and by reading your script, I suppose it works since you don't complain about errors in that part of the script. I should just go with " " whithout the () because no function (like rs()) is called.

Set rs = dbConn.Execute("SELECT FirstName, MiddleName, LastName, Nickname, Address, City, State, PostalCode, HomePhone, WorkPhone, MobilePhone, FaxNumber, AlternativePhone, EmailAddress, Notes FROM [CONTACTSList] WHERE FirstName = '" & Data & "'")

Seems like you want to get it all. Again, I don't have your mdb file, but if these are just about all the fields there are, I would change all the names of these fields into one single *. SELECT * FROM CONTACTSList WHERE FirstName = bla bla