insert records to access DB from forms 2

[koolkebab]

Hi guys,
I am a newbie programmer in VBA and facing a lot of difficulties with it. I am creating a simple project which allows the user to add, update, delete and find records from a set of tables.
I am working on the Insert data module. I essentially have to populate 2 tables from the inputs given by the user on Access forms.

Problems:
1) One of the inputs in the form is of attachments (Access 2007). I dont know how to read and store these in the table. I also have text boxes, drop downs and memo type variables in the table. How should I read the information form the form to store it in a table. This is the code I am using but does not work.

Private Sub AddRecord_Click()

DoCmd.RunSQL "INSERT INTO tblManagerInfo ([FirstName],[LastName],[StAddress],[State]," _
& "[Country],,[Phone],[Fax],[Website],[AssetsUnderManagement],[Notes])" _
& "VALUES (AddFirstName,AddLastName,AddStAddress,AddState,AddCountry,AddEmail," _
& "AddPhone,AddFax,AddWebsite,AddAUM,AddNotes)"

DoCmd.RunSQL "INSERT INTO tblInvestment ([FirstName],[LastName],[FundName],[StrategyMainCategory]," _
& "[StrategySubCategory],[MinCommitment],[TargetReturn],[Leverage],[Fees],[Geography]," _
& "[Likelyhood],[Status],[Source],[OtherLPs])" _
& "Value(AddFirstName, AddLastName, AddFundName, AddStrategyMainCategory, " _
& "AddStrategySubCategory, AddMinCommitment, AddTargetReturn, AddLeverage, AddFees, " _
& "AddGeography, AddLikelyhood, AddStatus, AddSource, AddOtherLPs)"

End Sub

2) I also have a criteria that if the certain fields (PKs) are not populated then the Inserts operation should not go ahead.

Code:
If AddFirstName = Null Or AddLastName = Null Or AddFundName = Null Then
X = MsgBox("Please Enter the Fields with the * mark", vbCritical, "Incomplete form")
End If

Even before this runs the DoCmd.RunSql command runs, even if I out the DoCmd.RunSql in the Else part of the If statement. I just cannot figure this out. Its kinda frustrating. Please help.

Thanks,
Koolkebab

 

[fneily]

I'm not sure why you don't have bound forms to your tables. Why are you using code?
Also, are the FirstName and LastName in both tables the same? They shouldn't be. Violates Normalization.

 

[SoCalAccessPro]

I never, or at least rarely, tie a form directly to a table or database.

Too much potential to accidently change and/or erase data.

Unbound form + data validation always works better, although it's more work to implement. IMO.

koolkebab, where are the addXXXX values coming from ? Are these from a text box on yur form ?

2) I also have a criteria that if the certain fields (PKs) are not populated then the Inserts operation should not go ahead.

Acess won't let you anyway, it'll error out if you try an insert without a PK.

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[koolkebab]

Thanks for your responses.

@ fneily: FirstName and LastName are both text boxes. Could you please elaborate more on bound forms. Do you suggest I have some object access the tables rather that the form directly accesing the tables.

@ SoCalAccessPro: addXXXX are the names of the texboxes, comboboxes, attachments on the the form. Do you suggest I have some object access the tables rather that the form directly accesing the tables. Also Access errors out but I want to handle the error using a code but it doesn't let me to.

Regards,
koolkebab

 

[SoCalAccessPro]

OK, then a few things:

1. Make sure you nclude your primary key, or your insert statement will fail. But you already knew that.

2. Explicity reference your text boxes, etc.

me.AddFirstName,me.AddLastName,me.AddStAddress,etc.....

3. Place a breakpoint in your code and tab through using F8 until the error is generated. At that point, go to the immediate window and type ?err.num.Write this down. Then, in your error trap, have somethjing like

If err.num = the error number you wrote down then
msgbox "Add Failed"
Exit Sub
End If


4. Your logic for verifying the textboxes looks good. Just add an exit sub and maybe a message box. Also, even though it us unsightly, I usually just "stack" my validation. I also check for empty string as well as null.

If AddFirstName = Null or AddFirstName = "" then
msgbox "Oops"
Exit Sub
End If

If AddLastName = Null or AddFirstName = "" then
msgbox "Oops"
Exit Sub
End If


Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[koolkebab]

Thanks!

But there are some issues:
By adding "me." before every object VB keeps popping a textbox and does not pick the value entered in the form text box.

Also, I wanted to know if using VBA and Access, this is the best way to perform the following tasks: add, update, delete and find records.

Regards,
Gaurav

 

[Remou]

I suggest re-examining fneily's comment. Why are you not binding a query or recordset to your form? It is by far the easiest.

 

[fneily]

Do you know what the form wizard is?
For Firstname and Lastname I meant is the DATA the same? John Smith and John Smith is both tables?

 

[koolkebab]

@ fneily: Firstname=John and Lastname=Smith. And yes, Firstname and Lastname are names of fields with same data in both the tables.

@ Remou: How can I bind the query or recordset to my form. Is it done by using ADO?

 

[Remou]

You can simply pick the name of the query from the list available under the Record Source property, Property Sheet, form design view.

Forms will be build for you with this set up if you use the wizard, as mentioned by fneily.

 

[koolkebab]

Thanks!

Could someone suggest me some book (preferably online source) where I can get the syntax for VBA. I have the logic clear in my head but its just the syntax which is troubling me.

 

[Remou]

Here is one:

http://www.microsoft.com/downloads/...a0-5e58-4e69-b90e-c42ec7dbf887&displaylang=en

 

[fneily]

You might also want to read:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Again, you should not have the same names (data) in two tables.

 

[koolkebab]

One more doubt:
I have 2 combo boxes. Based on one value selected the combobox has to populate itself using a query. The query is:

SELECT tblStrategy.Sub_category
FROM tblStrategy
Where tblStrategy.Main_category = Forms!Detail!Add!AddStrategyMainCategory
ORDER BY tblStrategy.[Sub_category];

So the 2nd combobox having the Sub_category will change as an when the Main_Category changes. Now I have a form named 'Detail', in that I have a tab named 'Add' and in that I have the comboboxes.
How am I supposed to handle this?

 

[Remou]

Have a look at this:

http://www.mvps.org/access/forms/frm0031.htm

Tabs do not count, nor do form sections.

You will need to run some code in the after update event.

There is a lot to be said for carefully studying the Northwind sample database that ships with all versions of Access.

 

[koolkebab]

Thanks. Based on Remou's comments I wrote the query in the Change event of the MainCategory combo box to change the value of the SubCategory combo box on the fly as the user choses a value in the MainCategory combo box.

Private Sub AddStrategyMainCategory_Change()
AddStrategySubCategory.Value = Docmd.RunSQL "SELECT tblStrategy.[Sub_category]" _
& "FROM tblStrategy " _
& "Where tblStrategy.Main_category = Me!AddStrategyMainCategory " _
& "ORDER BY tblStrategy.[Sub_category]"

End Sub

I am getting a 'compile error:Expected end of statement' as an output. Any comments ?

 

[Remou]

You cannot set a control with SQL in such a way. You will either need a recordset or DlookUp:

Private Sub AddStrategyMainCategory_Change()
    AddStrategySubCategory.Value = DlookUp("[Sub_category]","tblStrategy","Main_category =" & Me!AddStrategyMainCategory
End Sub

You will need single quotes if Main_category is a text field.

 

[koolkebab]

Certain issues/corrections:

I had the wrong property being used up there.. It should be .ADDITEM rather than .Value

Private Sub AddStrategyMainCategory_Change()
AddStrategySubCategory.AddItem (DLookup("[Sub_category]", "tblStrategy", "Main_category =" & Me!AddStrategyMainCategory))
End Sub

Also the DLookup gives us only the first occurence rather than giveing all the values the criteria satisfies.
Still gives an error

 

[Remou]

Why not set the rowsource of the combo in that case?

AddStrategySubCategory.RowSource="SELECT tblStrategy.[Sub_category] " _
    & "FROM tblStrategy " _
    & "Where tblStrategy.Main_category = Forms![Name Of Form]!AddStrategyMainCategory " _
    & "ORDER BY tblStrategy.[Sub_category]"

The Row Type will need to be set to Table/Query.

If you want to use add item with the SQL you will need a recordset to loop through.

 

[koolkebab]

I had tried that previously with this code:

Private Sub AddStrategyMainCategory_Change()
AddStrategySubCategory.RowSource = "SELECT tblStrategy.[Sub_category] " _
& "FROM tblStrategy " _
& "Where tblStrategy.Main_category = Forms![Detail]!AddStrategyMainCategory " _
& "ORDER BY tblStrategy.[Sub_category]"

End Sub

But it led to a textbox popping up asking the value of Forms![Detail]!AddStrategyMainCategory