Full Version: Subform won't allow new records to be created
UtterAccess Forums > Microsoft® Access > Access Forms
kitana78
Hi

Have a question here.
I have a main form that is based on PA (Product Advisor) that will list records of sales made by the PA in a subform that is based on a query of Sales, Item Master and Customer Master.

My problem is the subform does not allow me to create new sales record for the PA. Properties for the subform allow deletions, additions and edits. I have no idea what the root of the problem is. Does anybody have any clue? frown.gif
RuralGuy
It sounds like the query the SubForm is based on is *not* updateable. Go to the RecordSource of the SubForm and invoke the QueryBuilder "..." button. In the DataSheet view, try and change something. If it just beeps then it is *not* updateable. Key in Troubleshoot queries in the Answer Wizard for Access (notVBA) and there are some answers for you.
kitana78
From the tips u gave in access help, it seems that my query was based on 3 tables that has many-one-many relationship. That explains a lot. Thanks. frown.gif
nother question:
on the same subform, I tried to lookup and display unit price for product item based on the location of the company that ordered the product. So for my combobox of product item:
Private Sub cmb_ItemCode_AfterUpdate()

Dim strFilter As String
strFilter = "ItemCode = " & Me!ITEMCODE

If Me!Location = "Value1" Then
Me!Unit_Price = DLookup("URSP-EM", "Item Master", strFilter)
Else
Me!Unit_Price = DLookup("URSP", "Item Master", strFilter)
End If

End Sub
Is that the correct way to do it? If it is, it's not working. *Sigh.
Any help is appreciated. frown.gif
RuralGuy
If [ItemCode] is numeric then:
strFilter = "[ItemCode] = " & Me.cmb_ItemCode
If [ItemCode] is text then:
strFilter = "[ItemCode] = " & Chr(34) & Me.cmb_ItemCode & Chr(34)
You were not using the correct name for the ComboBox! If you use a dot rather than bang as I have,
then Intellisense will help you.
kitana78
Glad you pointed that out. I'm new to VBA and have no basic training. Usually I just search on the net and put what I think is logic and rational and hope for the best. smirk.gif
Have rewritten the code like what u said:

strFilter = "[ITEMCODE] = " & Chr(34) & Me.cmb_ItemCode & Chr(34)

If Me.State_Name = "Value1" Or "Value2" Then
Me.UnitPrice = DLookup("URSP-EM", "Item Master", strFilter)
Else
Me.UnitPrice = DLookup("URSP", "Item Master", strFilter)
End If

Is it because of my form structure that it is still not working? The UnitPrice still displays the URSP instead of URSP-EM like it should.
RuralGuy
If (Me.State_Name = "Value1") Or (Me.State_Name = "Value2") Then
kitana78
Sorry, but it's still not working.
RuralGuy
Put a breakpoint on the If statement and single step the code. Hover the cursor over the Me.State_Name and see what is in there.
kitana78
Yup.
I've put a breakpoint and step through the code. Me.State_Name contains "Value1" but Me.UnitPrice is not updating to URSP-EM. Weird.
RuralGuy
Change this line:
Me.UnitPrice = DLookup("URSP-EM", "Item Master", strFilter)
to:
Me.UnitPrice = DLookup("[URSP-EM]", "Item Master", strFilter)
When you single step, is that the line that executes?
kitana78
Oh no.
Now it's showing random numbers on the form and updated the URSP field in Item Master table as well. It didn't behaved like this before. I think there might be a problem with my Access. It's becoming very sluggish now. I've tried restarting the pc but it still behaving funny. I only changed 1 line of code. Is it because I also simultaneously open a database system that has crashed before and now it's making my Access to behave unpredictably?
Edited by: kitana78 on Wed Jan 18 2:10:44 EST 2006.
RuralGuy
It sounds like it is time to create a blank db with Tools>Options>General tab Name Auto-Correct turned OFF and import everything from your first db using File>Get External Data>Import...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.