How to populate Tables using Unbound Form

[ansar.abdulhai]

Hello all,

I want to use unbound form and when after every field is filled out in the form I want to now add that data in the table at backend. I am using Access 2007. How do I populate tables by using a button on unbound form?

Please help me!!!!!!!

 

[boblarson]

That's a lot of work. Why not use a bound form instead? You can use the Before Update event to validate and decide whether to let a save happen or not.

 

[pbaldy]

Searching here on "unbound" should turn up some code. You could create an append query that got its values from the form, you could use a recordset, or build SQL in VBA and execute that.

 

[ansar.abdulhai]

Thanks Bob and Baldy.

Bob: I don't want to let users view all the records from the table by using a bound form. Every time a user opens a form it should be blank and the user should not be able to view any record from the back end table. After entering data in the form then the user should press a command button and it should then populate the table with that data. That's why I need to create an unbound form.

Baldy: I tried to build SQL in VBA but I could not find provider. It's giving me error that Provider is not found. I think I don't have SQL server on my system. Don't know what to do? Please guide me....

Can I use macros to do this job??????

 

[pbaldy]

Presumably SQL Server would be on a server, not on your PC. You would need the appropriate connection string:

http://www.connectionstrings.com/

A macro could fire off a saved query, but that's probably it.

 

[ansar.abdulhai]

I have seen in the references that I have Microsoft OLE DB simple Provider 1.5, Microsoft OLE DB for OLAP. Basically I am very new to Access and programming VB. But I am learning fast. Do I need to select any of these as reference in VB Editor?


OH! By the way I am using Access 2007. How do I do this job in Access 2007?

 

[pbaldy]

I'm not sure what you're asking, but you don't need anything other than the default references to connect to SQL Server. Depending on how you are doing this, you may need the DAO or ADO reference.

 

[ansar.abdulhai]

Sorry for being so confusing!!!!!!!

Let me put it in clear manner. I am using Access 2007 to create database for a certain project. I have created the tables and also given the relationships between each table. I now want to create input methodology. For that I have to create forms. But if I create bound forms, the users of this database can see all the records of the table. I want to prevent other user who is on different project not able to see data for another project. For this I want to create unbound forms and create some buttons just below it so that once the data is entered, the user has to press the cmd button and then the event procedure under it should populate the corresponding table with that data checking some validation rules.

I don't know how to do that? Also, I didn't get to know how I can connect to SQL server. If you could please help me show the right direction to go I would really appreaciate that.

-Thanks.

 

[missinglinq]

Bob: I don't want to let users view all the records from the table by using a bound form. Every time a user opens a form it should be blank and the user should not be able to view any record from the back end table. After entering data in the form then the user should press a command button and it should then populate the table with that data. That's why I need to create an unbound form.

That's no excuse to use an unbound form! You're making a huge amount of extra work for yourself with no gain! If you want your users to only be able add new records without viewing existing records, all you have to do is set the form's Data Entry Property to Yes. Using unbound forms for data entry in Access does away with about 90 per cent of the power of Access.

If you're going to use unbound forms, you'd be better of, in a lot of ways, to use a Visual Basic or C# front end with MySql or one of the other SQL back ends. You can compile and distribute your front end, without the other users having to have Access on board, and better data security. But a whole lot more work, just like maintaining
unbound forms in Access.

 

[boblarson]

That's no excuse to use an unbound form! You're making a huge amount of extra work for yourself with no gain! If you want your users to only be able add new records without viewing existing records, all you have to do is set the form's Data Entry Property to Yes. Using unbound forms for data entry in Access does away with about 90 per cent of the power of Access.

Yep, that's my argument too. It makes me wonder if they like doing that extra work or if they just don't understand how to actually work with Access to give them what they want. Access is way more powerful than people give it credit for.

 

[ansar.abdulhai]

Wow Missinglinq! Thanks a lot. That did it.

I also need help about creating login forms. My database works in this fashion! I have created database for accomplishing an engineering project. Say a new project comes to my company. The project manager enters details of the project in one table and details of the team members who will be working on that project in another table by another form. Now I have connected each and every table in my database to this project table. In a project one employee has to fill few forms. I want login form so that whenever an employee tries to open one of these form it should ask project number first and login details. Once these are entered a cmd button should check these entries against the corresponding fields of the table and if there exists a match then only allow that user to open that form. How do I do that?


Please bare my stupid unclear language..... I apologize for it. Again I am a beginner in Access and learning fast with the help of people like you. Please guide me so that I can master this software......

Thanks