Data Entry Form

[loki1049]

Ok, I'm completely lost. I can't figure out the best way to go about this, and I am not very familiar with Access 2007 (VBA and Forms anyways) I do have a good working knowledge of SQL. I have a bunch of questions so maybe I'll just index them cause they might be pretty random.

1.) Whats the best way to use a 'BLANK' form for inserting multiple records?

--The way I have tried is first binding the form to the table I want to add a record to, but I have to add several records with one form interface and I don't think that this method will work. I have tried to instead not bind it at all and use drop downs and cascading drop downs to get the values, then I would Ideally want to use a button and on its click event run some SQL code. I think this would be the easiest, but all attemps have failed. Here is a sample of what I have tried:

First make a button, then click its properties and in events table, under On Click use the code builder to do this:

Private Sub EnterRecordButton_Click()

INSERT INTO PREquipment(EquipmentName,PREquipmentTypeID) SELECT [Forms]![AddEquipment]![Add_EquipmentName],2"

End Sub

Obviously it doesn't work.

2.) What is the code builder exactly? Is it visual basic code.. is this the same as what you would write in visual studio?

3.) Do I have to open the database or something before I can run SQL, I've seen examples with

Dim dbs AS Database

And then some open command.. err I'm lost. I have used C++,C,Java but I never used VBA if thats what this is.

 

[CraigDolphin]

1.) Whats the best way to use a 'BLANK' form for inserting multiple records?

Try changing the default view property of the bound form to 'continuous view' (Form Properties>Format>Default View). This allows multiple records to be entered/visible

If you only wish to have the form be used for entering new records and not show existing records, set the 'Data Entry' property of the form to Yes (Form Properties>Data>Data Entry). If you want both existing and new records to show, set this property to No.

2.) What is the code builder exactly? Is it visual basic code.. is this the same as what you would write in visual studio?

The code builder is the area where VBA code modules can be accessed and edited. There are two types of code modules. Some modules are linked to specific forms. Others are general purpose code modules. VBA is very similar to VB, but there are some small differences in syntax from time to time (or so I understand). VBA help, google, and this forum are helpful places to search for tips on specific functions etc.

3. Do I have to open the database or something before I can run SQL, I've seen examples with Dim dbs AS Database And then some open command.. err I'm lost. I have used C++,C,Java but I never used VBA if thats what this is.

If you are using Access as a backend then you can run SQL on the backend tables from your frontend as long as you've opened a connection to the db. Someone else may be able to talk more knowledgably than I about that issue.

You can also execute SQL commands from VBA code in the same database file, either in a form module or a general purpose (public) module. e.g., DoCmd.Run SQL strSQL (where strSQL is a string containing the transact SQL to be executed). And finally, predefined SQL statement (queries) can be named and stored in the database. These can be used as record sources for forms, reports, exported as spreadsheets/text files, and the underlying sql can be manipulated with vba.

Your questions are extremely broad, btw. I would suggest finding a good reference book on how to use access and reading it would be a productive exercise to go through.

 

[loki1049]

Thanks, I really just needed some guidance. I have decided to go with using SQL to add new records to my table which is unbound. The main issue I am having now is how to go about calling the SQL.

The only way I could figure it out is by using the following code in a module that is called on a On_Click event of a button

Private Sub Button1_Click()

DoCmd.RunSQL "INSERT INTO FakeTable ( FieldID, Field1 )SELECT 1,'Field1 data' "

End Sub

This doesn't seem so eligant, as I can't even let the string definition go to a new line or I get errors. And, I eventually hope to use the values from my drop boxes to enter into my SQL statement somthing like this

DoCmd.RunSQL "INSERT INTO FakeTable ( FieldID, Field1 )
SELECT" & Forms!MyForm!MyComboValue.Column(0) & "1 "

Or something of that sort, I know I will have to fool with quoted identifiers to make sure it knows when the " end and the actual string definition is finally over, but I hope you get the idea.

 

[boblarson]

It would be this:

DoCmd.RunSQL "INSERT INTO FakeTable ( FieldID, Field1 ) [COLOR="Red"][B]" & _[/B][/COLOR]
" [COLOR="red"][B]Values(" &[/B][/COLOR] Forms!MyForm!MyComboValue.Column(0) [COLOR="red"][B]& ",[/B][/COLOR]1[COLOR="red"][B])[/B][/COLOR]"

And, for example, if your Field1 is a text field you would need quotes:

DoCmd.RunSQL "INSERT INTO FakeTable ( FieldID, Field1 ) [COLOR="Red"][B]" & _[/B][/COLOR]
" [COLOR="red"][B]Values(" &[/B][/COLOR] Forms!MyForm!MyComboValue.Column(0) [COLOR="red"][B]& ",'[/B][/COLOR]1[COLOR="red"][B]')[/B][/COLOR]"

 

[loki1049]

Ahh, this is giving me a head ache, it won't let me insert values from my form. I have two in particular that I have started testing with. The first is a text field which has the label 'Add_EquipmentName' and the other is a combo box which has the label 'Add_EquipmentTypeID'. The records source of the combo box is a select statement:

SELECT PREquipmentTypeID, PREquipmentType FROM PREquipmentTypes;

It returns 2 columns and has the first column bound, then hides the first column from the user so that when they click on it they see the name, and when that name is selected it associates the correct ID for the equipment type.

Now, in my VBA module on a the click of a button I run the following

Private Sub Command20_Click()

DoCmd.RunSQL " INSERT INTO PREquipment " & _ 
" ( EquipmentName, PREquipmentTypeID ) " & _
" SELECT " & Forms!AddEquipment!Add_EquipmentName & " , " & _ 
" Forms!AddEquipment!Add_EquipmentTypeID "

Add_EquipmentTypeID = Null
Add_EquipmentName = Null
 
End Sub

And this fails, when I click the button it prompts to enter a parameter for the value that I have typed into my text box for Add_EquipmentName.

Does anyone on here have a link or something to how you access parts of forms from VBA?

I mean I have trouble figure out why I can't use Me!FormObject and instead have to use Forms!FormName!FormObject. Or when to use a '.' instead of a '!'. And how to pick columns of an Object, Me!Object!column(0) ? There has to be some good literature on this somewhere, or perhaps what this ./! designation is officially called so I can search for it?

 

[boblarson]

Okay, again I will state what I did earlier. You don't use SELECT when you are just providing values. You use VALUES:

Private Sub Command20_Click()

DoCmd.RunSQL " INSERT INTO PREquipment " & _ 
" ( EquipmentName, PREquipmentTypeID ) " & _
" VALUES(" &  Chr(34) & Forms!AddEquipment!Add_EquipmentName & Chr(34) & " , " & _ 
 Forms!AddEquipment!Add_EquipmentTypeID & ")"

Add_EquipmentTypeID = Null
Add_EquipmentName = Null
 
End Sub

 

[loki1049]

Oh, sorry. I didn't even notice that.