InfoPath Dev

Good day. I'm hoping to word this question properly. I'm building a form in Microsoft Infopath 2007 that links to an access 2007 database. What I want to do is when the user opens the form that

 

A:It automatically displays the last 10, 20, however amount of records I decided to return from the access database without having to manually submit the query before the records are displayed

 

B:Users can only enter new data and cannot edit records that are already sent back (make the returned records read only). Once they submit a record, they can't edit that record but can still see it.

 

I've attached a picture of what the form looks like. On the top table is where users can enter things into the form. The submit button runs a query thats linked to an Access data base that submits whatever is entered to Access and also returns the last 10 records of that data base to the table on the bottom (I have a filter applied to that table to display the last 10 records using the position() and last() functions). I'm able to make every field in that table on the bottom read only except the flight status which is a drop down box where under the properties I can't see a read only option like the other fields. So most of B in my question may be answered except that I want to make a drop down box read only. When I hit new record I also don't want those returned records to disappear like they do now. I always want to have the last 10 records displayed. Is there also a way to have the data work independently from both tables? When I enter data on the first table, it automatically gets put into the second one because they are talking to the same data fields linked to the access database. Is there a way to fix that? Appreciate any help given.

 

Edit: I've figured out how to disable the drop down box by applying conditional formatting by selecting the disable this control for the drop down box field (Flight status).

I think you should add another data connection to your database for item a - a receive connection. You could specify the count and sort in the query - when you create the connection, you'll see a little button on the dialog that lets you select columns that says "Edit SQL" - then you can modify the SQL a bit to be top 10 or top 20 or what have you. Your query could run on load & then you'd always have those records displayed. If the user submits more data, you could execute the query again in a rule to refresh.

Thanks for the response Hilary appreciate it! In the edit SQL box how/what do I enter to be top 10 or top 20? This is what I get when making the second data connection in the edit SQL

 

select [ID],[Current Date] as [Current_Date],[Traffic in and out time] as [Traffic_in_and_out_time],[From],[To],[Flight status] as [Flight_status],[Summary],[Time submitted] as [Time_submitted] from [Table1] as [Table1]

Also I'm wanting in my time field to display a 24 hour timeframe. When users enter a time its automaticall in the format _:_ so that if I was to enter 1545 then it would automatically be 15:45 with the colon. Or the colon is always in there and when I type 2 numbers for the hours it automatically tabs over to the minuets. Is there a way to set it up for that? 

You'll want to order the table according to what defines your top 10 (or whatever) records - you can do that before you click the Edit SQL button - if I recall correct, the button says something about modify on it and opens a dialog where you can specify the order. I think the Access syntax is the same as SQL:

Select Top 10 ...

So, just add Top 10 or whatever after the word Select.

If you right click the text box for your time field, you can set the format. I think you'll have to experiment with some rules to add the ":" if it is not present.

For now I'm just going to tacle the time issue. I can't seem to get the format _:_ to work. After fooling around with the concat and substring functions I'm still at a loss.

Once again how can I create a number format that will auto enter a colon for entering time. Example, I want to type "1400" and when I leave the field it goes to "14:00". It has to be done without VBA since I do not have the use of VBA with infopath on my computer however after doing a brief web search, this doesn't look possible without using VBA. Anybody know?

Well - it is messy. If I enter 900, the colon is supposed to go after the first digit, and you'll need a 0 before the first digit. If I enter 1400, it has to go after the second digit. Here's a sample - right click the link in the post header, save it locally. Right click the saved file and open in design mode - from there you can look at the rules on field1 and the formatting on the control and preview it to check it out.

Thanks Hilary thats exactly what I want to do, and it works the way I want it to in my form with the colon automatically appearing however I'm getting an invalid data red box appearing around my text box when I just type in a time without a colon and when I manually type in a colon there is no red box. This may have something to do with my form being linked to an access database thats putting in a different time format thats binded to whatever that format is in access. Under the text box properties under binding, my data type says optional_dateTime.notz and is greyed out whereas in your form the data type under the binding says Time (time) and is not greyed out. I'm not sure if this is the problem but its the only difference in terms of the properties go. Heres a picture of the 2 text box properties that show what I'm talking about, the one on the left is from mine and the one on the right is the sample you posted.

Disable the rules. Enter time in the report time field, with the colon, so you don't get the validation error. Use File > Save As to save the XML. Open the XML in a text editor like Notepad. Search for Report_time to find the node. What's the data look like?

One line of data shows Report_time="" and then a line of data below it shows Report_time="1899-12-30T15:25:00"

Euw. Well, that't the trick, then - when you modify what the user is entering via rules, you need the data to look like what the form expects for validation. Looks like it needs to be a date time - so you'd preface the time value sample I gave you  with 1899-12-30T, you could just concat that in, I suppose - and you could probably use today() instead - check and see what your data in Access looks like.

Thanks Hilary I almost got my form working like a charm! I get no validation errors now when the user just types a valid time and the colon gets added in automatically. The only thing now is when they enter an invalid time using 4 numbers (for example 9999) then 1899-12-30T99:99:00 is what shows up in the text box with the validation error. Is there a way to have it not display it like that but just 9999 with the validation error? Maybe I'm just being picky with the way it should look but it just looks messy when the 1899 thing is returned. Heres the modified coding, concatting in 1899-12-30T that you said to put in that I'm running for both the rules for adding in the colon automatically 

Rule 1: concat("1899-12-30T", "0", substring(., 1, 1), ":", substring(., 2, 2), ":00")

Rule 2: concat("1899-12-30T", substring(., 1, 2), ":", substring(., 3, 2), ":00")

You could also use today() instead of 1899:

Rule 1: concat(today(), "T", "0", substring(., 1, 1), ":", substring(., 2, 2), ":00")

I think you could check to see if something was invalid in your rule conditions - if the number entered is greater than 2400, perhaps?