dots and bangs 1

[tful282]

I am having a hard time passing an object to a subroutine/function which is referred to using the bang operator. I have a number of comboboxes using lookup tables on a form and I want to use just one subroutine/function to do the NotInList checking before adding. Here's some code so far:

Sub ItemNotInList(NewData As String, Response As Integer, Tbl As String, FldName as ???)
Dim Msg As String, Answer As Integer
Dim dbs As Database, rst As DAO.Recordset
Answer = MsgBox(StrConv(NewData, vbProperCase) & " does not exist." & Chr(10) & "@Do you want to add it?@ ", _
vbQuestion + vbYesNo + vbDefaultButton2, "Item Type&quot
Response = acDataErrContinue
If Answer = vbNo Then Exit Sub
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(Tbl, dbOpenDynaset)
With rst
.AddNew
!FldName= NewData
.Update
.Bookmark = .LastModified
.Close
End With
Response = acDataErrAdded
dbs.Close
End Sub

Now, what kind of variable should FldName be, and how can I set the value of rst!FldName ?
Thanks in advance for the help.

 

[DougP]

try this
I don't like that "with" stuff

rst.Addnew
rst!fieldname = NewData
rst.update
rst.Bookmark = rst.LastModified 'not sure if this is right
rst.Close

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[tful282]

Thanks DougP for the suggestion, however it has not fixed my problem.
I cannot seem to match variables correctly.
What data type should FldNme be?

Maybe I am trying to call the sub incorrectly:

ItemNotInList NewData, Response, "LookupTblName", "FldName"

Any other ideas?
Thanks

 

[preebs]

I had a few messy problems with this in the past. Try passing "FldName" as a field, not a string:

...FldName as Field

I hope this helps.

 

[preebs]

PS - when you are calling the field from a variable, don't use any dots or bangs. Let me know if you'd like to see a code sample of how this works.

 

[tful282]

Preebs,
Thanks, I would appreciate seeing some code. I can't seem to get/pass the field correctly.

Muchas gracias

 

[tful282]

Well, I stumbled onto a solution that works by passing the field name as a string.

Sub ItemNotInList(NewData As String, Response As Integer, Tbl As String, FldName As String)
....
rst.Fields(FldName) = NewData
....

It all seems so simple now!!!