Form looking for Parameters in field name that was changed.

[klarae99]

Hi everyone, I am new to Access 2003 (though my time on scripts has helped alot) and am creating an inventory database. In my original table design I had a field tblTrans.Date which I was advised to change as Date is a function. I changed the field name to TransDate. All of the previously created queries and reports automatically updated to use this new field name as a reference and still work fine.

Now I am creating a new unbound form to find a specific Item; the form has two cascading comboboxes that select vendor and then item number. I created a command button that theoretically opens frmProduct using the item combobox to filter the results (I used the command button wizard to create this). When I click the button a prompt asking for the parameter tblTrans.Date pops up.

I have looked in all of the event procedures, queries, and rowsources I can think of to find where the this reference is and have had no luck.

Is there any way I can 'search' for this 'field reference' or view the sequence of events for everything involved to try and locate this reference?

Does anyone have any ideas about how I might fix this issue? I would appreciate any advice or thoughts. Thanks!!

[nico5038]

There are some places where variables can hide.
When the form is based on a query, open the query and double-click the grey area besides the table(s). A properties window should show and there sometimes the sort or filter still holds an "old" variable.

Nic;o)

[klarae99]

Thanks for the tip, the form I am working with is an unbound form, and I had already checked the queries for the comboboxes that were on my form.

Your advice got me thinking though and I realized that the form I was trying to open up (frmProducts) had a subform (frmTrans) sorted by the transaction date. I opened frmtrans and it still referenced Date so I changed it to TransDate.

I no longer get the prompt for TransDate but I still get a blank form when I click on the OK button. I tried to create a new OK button after closing everything and saving changes but I still got a blank form (filtered and only displaying 1 record). I also created a new form with the same two comboboxes and a completely new OK button and it also opened a filtered blank form.

I also noticed that in the subform trans, the sort by date is not working.

Do you have any ideas why either of these things may be occuring and if you do, how do I fix them.

Thanks for all your help!

[nico5038]

Make sure that not only the Controlsource has been changed, but also the Name property (see under Other tab).
Also single-click the subform and check under the DataTab the linkage fields to hold the correct fields.
Finally check the blank form's properties under the Data tab, the "Data entry" property needs to be set to "No".

Nic;o)

[klarae99]

Nic;o)

Thanks for the ideas, I looked at all of the tips that you gave me, the Name property did not match the control source of my transdate field so I changed it. All of the other settings that you suggested I verified and no changes were necessary.

I again tried to pull up a record and opened a blank form again. Any other thoughts?

[nico5038]

Nope, best to zip and attach the .mdb so I can have a look.

Nic;o)

[klarae99]

Thanks so much for your offer!

From passed experience I know that I can not zip my whole database and get it small enough to attach to a post, I will go through and create a new database to import the relevant tables, queries and forms to and zip that to see if it will be small enough for you to review.

It may take me a little while but I hope to have it up soon!

[nico5038]

Strange, no TransDate popup for me :-(

I do see you're using a macro to open a form, better to use:

Private Sub btnNewItem_Click()
 
    DoCmd.OpenForm "frmproduct", , , "VendorID=" & Me.VenID
 
End Sub
 

Passing the VendorID will fill the info on the frmProduct.
Perhaps checking my sample .mdb migh show an alternative way to select Vendors. Personally I would have a Vendor form with the products as a subform.
http://www.geocities.com/nico5038/xS...tAction-97.zip
and
http://www.geocities.com/nico5038/xS...et-Combo97.zip
I use the Object/Action in general and give my users this description so they can select the row(s) themselves:
http://www.geocities.com/nico5038/xR...nstruction.zip

Nic;o)

[klarae99]

Nic;o)

Thanks for the resources I will look into them to see what I can glean as soon as I get back on a computer with Access loaded. I will also see about changing the Macro to code.

As for you not getting the TransDate popup, I don't either (see post Oct. 22), my issue now is that even when I enter data into the comboboxes on my frmaddedit, when I click OK I get a blank Product form (1 record filtered) instead of the form with that products information. I have tried to delete and recreate the command button, and still get the same results. Did you also have this problem? Do you have any suggestions about what may be happening if you don't?

All your help and advice is greatly appreciated. Thanks!

[nico5038]

Just try the proposed code from my previous post and see the effect :-)

Nic;o)

[klarae99]

Nic;o)

Sorry for the long delay in getting back to you, my boss was in town and I had to shift my attention to other things for a while. I am back to working on the database now.

I am confused regarding your last post...I am using macros with btnNewItem and btnNewVen, both of which work as I expected them to, when I changed btnNewItem to the code you suggested I ended up with all of the items that that paticular vendor already had in inventory, which is not what I wanted to see.

The button I am having trouble with is btnOK, which has its current on click procedure as the following code:

Private Sub btnOK_Click()
On Error GoTo Err_btnOK_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "FrmProduct"
 
    stLinkCriteria = "[Item]=" & "'" & Me![ItemID] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_btnOK_Click:
    Exit Sub
 
Err_btnOK_Click:
    MsgBox Err.Description
    Resume Exit_btnOK_Click
 
End Sub

Unfortunately when I click on this button I always get a blank form, not the form with the corresponding item number. I really do not understand why. Any help would be appreciated.

[nico5038]

Nasty one, you've used "qryitembyvendor" with a VendID and an ItemID as source.
The boundcolumn is however the first column, so the vendorID ('25) is passed instead of the ItemID :-(
So:
1) Change the Bound column for the ItemID combobox from 1 to 2
2) Change the stCriteria to trigger the frmProduct into:

    stLinkCriteria = "[VendorID]=" & Me.VenID & " and [Item]=" & "'" & Me![ItemID] & "'"
 

Nic;o)

[klarae99]

Thank you! Thank you! Thank you!

Its working great now, thanks for getting this to work for me!! I REALLY appreciate all the time you dedicated to this!

Also, I will work on changing the macros for the other two buttons to code, I know that most people who work regularly with access prefer code to macros but I needed to get this to a working level and my codeing skills (as you can see) are still very early in their development. Now I can go back and tweak things and my boss won't get so agravated with me takeing too long.

Again, thanks for all your help with this!