Looking for Microsoft Access and InfoPath experts.

I’m in the process of creating a database at work and I’ve run into a couple of issues. I’ve tried Googleing for solution but so far I’ve gotten nowhere. Hopefully someone here can help.

I’m using an InfoPath form for data entry and another one to display existing data in a table. The first problem is that I can’t search my data, which means manually going through the table to find anything I need. The data is text based and I would like to be able to do a single search of all the database fields and have the search generate a table (exactly like the one I already have to view my data) only with data that is related to my search criteria.

 Spoiler

Screenshot of the Homepage Form


Screenshot of the data entry Form


Screenshot of the database table view Form

The second issue has to do with Access. I want Access to generate a Reference number for each entry to the database, a simple alphanumeric combination that could be used as reference on other documents. I want the Ref # to generate automatically using the first letter of every word in the “Audit Process” field (this part I have) and the number from the primary key field in Access called “ID” (this is an autonumber field and also works fine) that way no Ref # is ever identical but entries are grouped by their respective processes. In order to generate this Ref # I’ve created a form in Access and inputted the required formula into the Ref # field, as I said the formula works just fine and the Ref # generates are needed in the Access form. The problem is that the Ref # field in the Access Table doesn’t update with the required information from the Access Form (in the same database). I was told that because there is a Parent>Child relationship between the Access Table and Form (respectively) that this is why the update of the Ref # field is not taking place. But the other fields update just fine when I use the Form to input data, so I’m confused. Originally I wasn’t using the Access Form since I really have no use for it, like I said InfoPath will be the data entry medium used once the database is implemented but the formula used to generate the alphanumeric reference number doesn’t work if I input it directly into the field in the Table, so this is just a work around, if anyone can offer a more efficient way of getting it to work that would be great.

 Spoiler

This is how the SQL code should work (Form view)


This is the SQL code NOT updating the database Table

For #2 you would just created a calculated field.

You don't need to manaully re-enter something that's made up of data already present in the row.

http://allenbrowne.com/casu-14.html

For #2 you would just created a calculated field.

You don't need to manaully re-enter something that's made up of data already present in the row.

http://allenbrowne.com/casu-14.html

That's the thing I'm not re-entering it. The Ref # is supposed to automatically generate using data from other fields and it does that. The problem though is that it doesn't update the data Table from the data Form, so when I try to call up the data and view the Ref # in InfoPath it doesn't pick it up.

What you're not getting is you create formula field in the table, not in the form.

I thought I mentioned that I tried doing that, it doesn't work. If you take a formula like the one in the screenshot and try inputting it into the table instead of the form you'll get an error saying something along the lines of "invalid field name". According to the Microsoft help page this is a normal error so the only work around is to use a form.

The obvious question then is 'what versions are you using'?

2007, but I think someone tried it for me in 2003 and it didn't work either.

It won't work before 2010 as the article says.

Access is almost never version consistent.