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

 

[SoCalAccessPro]

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

Was the form open ? The form has to be open in order to pass the parameter.

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[koolkebab]

The combo boxes are on the tab control which are on the Form, so the forms were open when I chose a value from the Main Category combobox and when I click on the Sub Category combo box, a textbox pops up asking the value of Forms![Detail]!AddStrategyMainCategory, which it shoould infact pick up from the valu selected by me in the Main Category combobox.

 

[Remou]

What is the name of your main form? Is it called Detail? This is a reserved word.

 

[koolkebab]

It is Detail. But I now tried by changing it to Detail1. Still the same result.

 

[koolkebab]

The actual location of the combo boxes is, if it helps:

Detail1 > TabControl > Add > Combo box

where Detail1 - Form
TabControl - Tab Control
Add - Name of the tab

 

[Remou]

Ok. I was not paying attention. The form bit must be outside the quotes:

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

As I showed above. Is Main_category a numeric or text field?

 

[koolkebab]

Both Main and Sub are Text fields.

 

[koolkebab]

hey Remou: I finally figured it out:

AddStrategySubCategory.RowSource = "SELECT tblStrategy.[Sub_category] " _
& "FROM tblStrategy " _
& "Where tblStrategy.Main_category = '" & Me!AddStrategyMainCategory.Value _
& "' ORDER BY tblStrategy.[Sub_category]"

gave me the right results. Thanks. will keep you updated with more problems, if any.

 

[Remou]

In that case you need single quotes:

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

Sorry it is late. I have now confused myself between the recordset and the rowsource. The rowsource can include the form name in the quotes, the recordset cannot. However, try the above. It should work if Forms![Detail1]!AddStrategyMainCategory is the correct reference. You can step through the code to check.

 

[koolkebab]

I really apologize if my doubts are really basic, but here come another one:
I want to reset the Sub Category combobox if the selection in the Main Category Combo box is deselected.
So I added this condition:

If Me!AddStrategyMainCategory.Value is Null Then
AddStrategySubCategory.RowSource = "SELECT tblStrategy.[Sub_category] " _
& "FROM tblStrategy " _
& "Where tblStrategy.Main_category = 'XYZ' _
& " ORDER BY tblStrategy.[Sub_category]"
Else
AddStrategySubCategory.RowSource = "SELECT tblStrategy.[Sub_category] " _
& "FROM tblStrategy " _
& "Where tblStrategy.Main_category = '" & Me!AddStrategyMainCategory.Value _
& "' ORDER BY tblStrategy.[Sub_category]"
End If

Here the 'XYZ' is a nonexistant value in the table, thus the result of the query gives us no records. It does not give me any error but does not give me the desired results.
I also tries = "" and =" " instead of the 'is Null' condition.

Thanks

 

[koolkebab]

I have one more doubt:

I have a form that has text boxes which are bound to 2 seperate tables. I have written a macro to insert records in one table, but cant figure out a way to populate the other table. (1st table is the one with which the form is linked). I am trying to use the RunCode in the Macro and write a function to do the needful:

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

But this function is not recognized by Access.
Let me know your thoughts. Thanks

 

[koolkebab]

The above problem has been solved. Now I have another problem.
I want to have a sub form and want the record source to be a query. I also have an Attachment type variable. I am getting all the fields correctly except the Attachment field using the query. Please let me know how is this possible.

Thanks

 

[Remou]

You should use IsNull:

If IsNull(Me!AddStrategyMainCategory.Value) Then
    AddStrategySubCategory.RowSource = "SELECT tblStrategy.[Sub_category] " _
    & "FROM tblStrategy " _
    & "Where tblStrategy.Main_category = 'XYZ' _
    & " ORDER BY tblStrategy.[Sub_category]"
Else

Why not just set the rowsource to nothing:

AddStrategySubCategory.RowSource =""

Why not use an event rather than a macro? They are much easier to debug. For example, your SQL uses Value instread of Values and is short of a couple of spaces.

Function InsertRec()
strSQL = "INSERT INTO tblInvestment ([ManagerName],[FundName],[StrategyMainCategory]," _
& "[StrategySubCategory],[MinCommitment],[TargetReturn],[Leverage],[Fees],[Geography]," _
& "[Likelyhood],[Status],[Source],[OtherLPs]) " _
& "Values ( AddManagerName, AddFundName,  AddStrategyMainCategory, " _
& " AddStrategySubCategory,  AddMinCommitment,  AddTargetReturn,  AddLeverage,  AddFees, " _
& " AddGeography,  AddLikelyhood,  AddStatus,  AddSource,  AddOtherLPs)"
 DoCmd.RunSQL strSQL
End Function

There is a lot to be said for looking at the Northwind sample database that ships with all versions of Access it will often show you an easier way of doing things.

 

[koolkebab]

Thanks.
Now there is this problem which is driving me crazy. I just dont see any problem with it.
I have a form and a subform on it. The subform's record source is a query which pulls data from a table. There are some text boxes and combo boxes on the form and based on the inputs given by the user a Find button finds the data from the database and gives the output on the sub form.
The record source for the Sub for is:

SELECT tblInvestmentManager.*
FROM tblInvestmentManager
WHERE
(((tblInvestmentManager.ManagerName) Like ("*" & Forms!frmFindRecord!FindManagerName & "*")))
Or
(((tblInvestmentManager.FundName) Like ("*" & Forms!frmFindRecord!FindFundName & "*")))
Or
(((tblInvestmentManager.FirstName) Like ("*" & Forms!frmFindRecord!FindFirstName & "*")))
Or
(((tblInvestmentManager.LastName) Like ("*" & Forms!frmFindRecord!FindLastName & "*")))
Or
(((tblInvestmentManager.OfficeLocation2) Like ("*" & Forms!frmFindRecord!FindOfficeLocation & "*")))
Or
(((tblInvestmentManager.StrategyMainCategory) Like ("*" & Forms!frmFindRecord!FindMainStrategy & "*")
Or
(tblInvestmentManager.StrategyMainCategory) Like (Forms!frmFindRecord!FindMainStrategy & "*")
Or
(tblInvestmentManager.StrategyMainCategory) Like ("*" & Forms!frmFindRecord!FindMainStrategy)
Or
(tblInvestmentManager.StrategyMainCategory) Like Forms!frmFindRecord!FindMainStrategy))
Or
(((tblInvestmentManager.StrategySubCategory) Like ("*" & Forms!frmFindRecord!FindSubStrategy & "*")))
Or
(((tblInvestmentManager.Geography) Like ("*" & Forms!frmFindRecord!FindGeography & "*")))
Or
(((tblInvestmentManager.Likelyhood) Like ("*" & Forms!frmFindRecord!FindLikelyhood & "*")))
Or
(((tblInvestmentManager.Status) Like ("*" & Forms!frmFindRecord!FindStatus & "*")));

I cannot figure out the problem in this. Please help.

 

[Remou]

What does Access say the problem is when you step through? What line throws the error?

 

[koolkebab]

There are no errors. But the output on the subform gives all the records and not the filtered set of records.
Also the Find button click function looks like this:

Private Sub FindRecord_Click()
subfrmFind.Requery
End Sub

 

[koolkebab]

And my default for the text and combo boxes is "Nil". There is no record in the table tblInvestmentManager with the value "Nil". Thus with the default value when I click on Find button it does not change the recordset and gives me a full record set.

 

[Remou]

Change Or to And and see what happens.

 

[koolkebab]

I tried it before. It wouldn't make sense because the user can input any one of the inputs and get the output. If I mention And the user will never get the record because he will have to input all the input fields to get the particular record. I hope I am not confusing others, coz I have surely confused myself.

 

[Remou]

If you put Or and some controls are not filled in you have:

SELECT t.*
FROM tblInvestmentManager t
WHERE
t.ManagerName Like "**"
Or
t.FundName Like "**"
Or
t.FirstName Like "*Whatever*"
<...>

Which is all the records.

With And you should have:

SELECT t.*
FROM tblInvestmentManager t
WHERE
t.ManagerName Like "**"
And
t.FundName Like "**"
And
t.FirstName Like "*Whatever*"
<...>

Which is any manager, any fund, but only records with Firstname=Whatever.