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

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
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.
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)
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
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.
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)
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.
If (Me.State_Name = "Value1") Or (Me.State_Name = "Value2") Then
Sorry, but it's still not working.
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.
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.
Change this line:
Me.UnitPrice = DLookup("URSP-EM", "Item Master", strFilter)
Me.UnitPrice = DLookup("[URSP-EM]", "Item Master", strFilter)
When you single step, is that the line that executes?
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.
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.