Subform Jumping to first record field

[yahya263]

I have a form with a subform for tracking the stocks trading. I use the subform to enter new transactions. This was working fine until I have made some modifications in the design, which started giving me the trouble. Whenever I try to input any transactions by selecting a combo box in the subform, it takes me to the first record, even before completing my entries. I tried my best to solve it. But not successful. Please help ! I am attaching a sample file for reference.

 

[burrina]

If you remove the Order By for frmTransactions the problem will go away. You can sort via the qry Ascending or Descending for this!

HTH

 

[yahya263]

I removed the Order By from frm Transactions. But still the problem persists. After choosing the stock from the main form combo, when I select Purchase from the combo box of Sub form, the cursor goes to new record. It is not allowing me to enter TranDate, Qty etc. !

 

[burrina]

You also need to remove from the subform go to new record.

 

[yahya263]

I can't ! I need the sub form go to the new record on load, to add entries.

 

[yahya263]

I removed the go to new record from the subform and tried. Still the same result. Can someone help please?!

 

[burrina]

Can someone please help wont get you very far. Here is your db.

Good Luck With Your Project!

 

[yahya263]

Thanks Burrina. It is working fine.

 

[burrina]

Your Welcome.

Good Luck With Your Project!

 

[yahya263]

Hi Burrina

Sorry to bother again. I tried to apply the same to the master file. But not successful, though. Could you please explain what should I change in the code/properties and where ?

 

[burrina]

1. Remove go to new record in your subform OnCurrentEvent
2. Remove OrderBy on frmTransactions
3. Add go to new record using the OnLoadEvent for the subform
4. Sort qryActiveStock StockName Ascending

SEE Example DB I Posted...

 

[yahya263]

I changed all of them. But still no luck. May be I have to rebuild the db !

 

[burrina]

I have NO way of knowing what you have. If your able to look at the code on the db I gave you, it should work. Do you not know how to do that?

 

[yahya263]

Thanks. I will try again.

 

[yahya263]

I am attaching the zipped version of main db. What I am unable to achieve are:

1. When I select any stock from the combo box of the main form, it is not taking me to the new record in the sub form.

2. When I try to select the TranType in the sub form to update the records, it is not allowing to me to select in the first attempt. When selected after some tries, the cursor moves to an existing record and the new record jumps up!

I believe there are some serious errors in my code!

 

[burrina]

Don't know what is happening with your db. Here is a revised version.

HTH

 

[yahya263]

Thanks burrina. It's working perfectly now. Just one more help. When I add a new stock using the "Add New Stock" button, it will not appear in the Combobox suddenky, as the qty is zero. I have to go to Stocks table and add the purchase there, to get it in the combobox. Any work around using the same "Add New Stock" button ?

 

[burrina]

frmStocks and frmTransactions use different queries for the results is why!
I don't really understand your Schema so hard to advise. I would think that changing the record source for your Combo on frmTranasctions would do the trick but not sure that would meet your requirements. I would need a lot more explanation to understand.

 

[yahya263]

Hi Burrina

I will try to explain . Now my combobox is based on a query which will bring only the stocks which are currently active (means qty is more than zero). However, there are cases when I want to see all the stocks in the comb box (One such case is while we add new stocks). I think this can be achieved through a check box in the main form. If checked, it will change the record source of the combobox to some other query, which will bring all the stocks. Is it possible?

 

[burrina]

I would use another combo box myself. Use this for the AfterUpdateEvent of the new combo box named cbounlisted
Create a query named qryUnListed, add tblStock to it.
Add 2 fields, StockId and StockName/Ascending
Also add Me.Requery for the Combo box using the DoubleClickEvent.

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[StockName] = '" & Me![cbounlisted] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
[Forms]![frmTransactions]![Transactions Subform1].SetFocus
DoCmd.GoToRecord , , acNewRec

HTH