InfoPath Dev

Hi! I am trying to create an automatic number field that starts at 531. It's for a ticket support form. I need to program a text box field to increment everytime a new form is created, otherwise if a ticket is just opened not to do anything. I am using VBScript. The form is connected to an Access Database. Thanks,

Lisa Spencer
Programmer/Office Specialist

To do this, you will have to keep track of what the next ticket number is in your Access database. Then, in code you will update the ticket field (probably in the OnLoad event) by querying the database for the last ticket number and increment by one. Check out this SQL statement:

"SELECT ticketNum FROM ticketDatabase ORDER BY ticketNum DESC LIMIT 1"

---------------
Matt Faus
InfoPath Dev Texas

Can you explain the DESC LIMIT 1? Does the field need to be an autonumber in the table or just an integer? I also need to set my field on the infopath = ticketNum correct?

Lisa Spencer
Programmer/Office Specialist

"ORDER BY ticketNum DESC" = Order the records in descending fashion using the ticketNum column
"LIMIT 1" = Only return the first record

The field in the form will be a non-editable text field that holds an integer data type. In the OnLoad event you will check to see if the field in the form has a value. If it doesn't, run the above query which will give you the last ticket number used, increment by one, and then set the field to this value. The field should be non-editable because you wouldn't want a user to change it, but you can still update it via code. You could even use an expression box.

---------------
Matt Faus
InfoPath Dev Texas

I need to also set my field on the Infopath form to the TicketNumber, correct? I was talking about the field in my Access table. Should it be auto or just an integer? Can you help me with the syntax, does a ; go at the end of every line? Do you have any recommendations for a VBScript book? Thanks so much!

Lisa Spencer
Programmer/Office Specialist

Well, I've determined a ; doesn't go at the end of every line, but I'm still unclear on the syntax. I'm getting alot of ; expected errors.

Lisa Spencer
Programmer/Office Specialist

quote:

---

Originally posted by lisaspencer

I need to also set my field on the Infopath form to the TicketNumber, correct?

---

Yes, you are right.

The field in the Access database will be a regular integer - you will take care of the autonumbering within InfoPath. You could actually do it either way, but it would be a little more tricky if you did the autonumber in Access.

As far as syntax goes, I'm not too familiar with VBScript, I always use JScript. In JScript it would go something like this:

XDocument.DOM.QueryAdapter.Command = "_INSERT_SQL_STATEMENT_HERE_";
XDocument.Query();

var lastTicketNumUsed = XDocument.DOM.selectSingleNode( "/dfs:myFields/dfs:dataFields/d:_TABLE_NAME_/@ticketNum" ).nodeTypedValue;

XDocument.DOM.selectSingleNode( "XPATH/TO/TicketNum/Field" ).nodeTypedValue = lastTicketNumUsed;

For help with VBScript syntax you could access the help file within the Microsoft Script Editor by hitting F1.

Sorry I can't be more of a help, I've never worked with VBScript much.


---------------
Matt Faus
InfoPath Dev Texas

Can you explain this code to where I know what to change.
I could use JScript I don't know much about either one.


Lisa Spencer
Programmer/Office Specialist

I'm getting a JScript runtime error-"Xdocument.DOM.QueryAdapter
is null or not an object.

Lisa Spencer
Programmer/Office Specialist

You need to capitalize the D in XDocument.

---------------
Matt Faus
InfoPath Dev Texas

It is capitalized and I still get the error.
Can you explain the code, please?
Thanks,

Lisa Spencer
Programmer/Office Specialist

Your Access database contains all of the ticket numbers already used. To obtain a new ticket number for a form that a user is creating from the template you will query the database to determine what ticket number the new form should be assigned. You can put this functionality inside of your OnLoad event handler. However, since the OnLoad event handler fires even when you are opening a form that has already been created (and therefore already been assigned a ticket number) you will need to check to see if the form already has a ticket number before assigning a new one.

Your OnLoad event handler will follow this psuedo-code:
* if ticketNumField is blank, we must assign a ticketNum - if it already has a ticketNum, skip all this code
* Query database to find the largest ticket number
* Increment this ticket number by 1 and assign to ticketNumField

I hope this helps, if you are still having trouble with the syntax of whatever language you are using, I suggest studying some of the example forms included with InfoPath as well as several that are presented on our website in the HowTo section.

http://infopathdev.com/howto/default.aspx

---------------
Matt Faus
InfoPath Dev Texas

Thanks for responding. My code is similar to your psuedo.
I am getting an error "undeclared reference to namespace prefix dfs"
I am unsure how to reference the data source and run the query properly.
The data connection is a secondary data source. So I know I have to use Dataobjects.
My code is:
objDataObject = XDocument.DataObjects("qryGetTicketNumber");
XDocument.Query();
var lastTicketNumUsed = XDocument.DOM.selectSingleNode( "/dfs:myFields/dfs:dataFields/d:TicketData/@ticketNumber" ).nodeTypedValue;
lastTicketNumUsed = lastTicketNumUsed + 1
XDocument.DOM.selectSingleNode( "XPATH/TO/TicketNumber/Field" ).nodeTypedValue = lastTicketNumUsed;
I have two other questions. Will this put the new ticket number in the database? How do a reference
an expression box field? Thanks for your help! I am learning more everyday.

Lisa Spencer
Programmer/Office Specialist

You do know that "XPATH/TO/TicketNumber/Field" was just an example and you must substitute proper XPath to match your form's schema, right?

The new ticket number will not be put into the database until the entire ticket is submitted. The ticket number will be inserted along with every other piece of data.

You cannot reference contents within an expression box - these are simply for displaying text, other nodes, and functions.

---------------
Matt Faus
InfoPath Dev Texas

Can I do an XPath to the Expression box? Would it be something like this?
/dfs:myFields/dfs:dataFields/d:qryGetTicketNumber/@FirstofTicketID I get
an error-invalid location path.Thanks,

Lisa Spencer
Programmer/Office Specialist