Double click on a record in datasheet opens a form to enter data

[RuralGuy]

I think I'm having a problem with the terminology. A LookupField is a field in a table that has a lookup ComboBox defined on the lookup tab of the field. A LookupTable is a complete table that is used to "lookup" values to put in a field in another table. It is what a ComboBox has as the RowSource of the ComboBox control. Adding values to the RowSource of a ComboBox is what the NotInList event is used for. Maybe this link will shed some light on the NotInList event possibilities.

 

[pooh_bear]

OP,
This may help in creating a button in A2007 which links directly to a class module:
When running the create button wizard in design view on your form, simply drop the button on the form, but then click 'Cancel' on the first screen of the wizard, rather than selecting one of the macro actions.
You then end up with a button on the form, with no following action.
Right click on the blank button
Select 'Build Event' from the dialogue
Select 'Code Builder' from the Choose Builder dialogue box

Bingo!

You should now be in the class module for the 'On Click' event for the button.

You'll then need to go back and adjust the properties of the button (image, name, font etc) afterwards, as you have a fairly blank button at this stage.

Hope this helps

pooh

 

[snehal0909]

Sorry Pooh,
How can i utilize this?
I mean how is related to my particular database & question?

Thanks for your reply!

 

[pooh_bear]

Sorry - may have confused things.

I was replying to post #12 but didn't realise at the time, that the thread had moved on - so it might be a red herring I'm afraid.

You are right, however, about only macro actions being available in the create button wizard. If you want to go straight to VBA from the button on a form in A2007 you seem to have to cancel the wizard at the first step.

pooh

 

[snehal0909]

Thanks Pooh!

That tip will sure come in handy - macros can't do everything Vb can.

 

[snehal0909]

i am almost there with the new database with proper names (according to naming conventions).
Just one Q?
How did you make that main form (datasheet) auto update?

What code did you add? & where?
Also if you know any links, please post them as you usually do.
THEY HELP A LOT.

Thanks!

 

[RuralGuy]

Open the main form in design mode and press <ALT> F11 and you will see the code.

 

[snehal0909]

The code works when i update the record.
But when i add new incident, it does not come up on the datasheet.

I used your code & the same happened with my db. i have to re-open the form (frm Incident Management System) to see the newly added incidents.

is there fix to it?

 

[RuralGuy]

I would have expected the opposite! Updated records would not show until you closed the form but New additions would. The RecordSet needs to be refreshed after editing with a Me.Refresh.

 

[snehal0909]

i found this
DoCmd.Requery
Me.Refresh
but where do i put it?

under your code?

 

[RuralGuy]

The code in your main form should be:

Private Sub txtEdit_Click()

   Dim strForm As String
   Dim strWhere As String

   strForm = "Add Edit Incidents"

   If Me.NewRecord Then
      '-- Open the next form in Add mode
      DoCmd.OpenForm strForm, , , strWhere, acFormAdd, acDialog
      Me.Requery
      DoCmd.RunCommand acCmdRecordsGoToLast
   Else
      '-- Open the next form in edit mode with a WhereCondition argument
      strWhere = "[Incident ID] = " & [Incident ID]
      DoCmd.OpenForm strForm, , , strWhere, acFormEdit, acDialog
      [COLOR="Red"]Me.Refresh[/COLOR]
   End If

End Sub

A Requery would refresh the RecordSet and pick up any new and/or deleted records but also set the RecordPointer back to the beginning of the RecordSet. The refresh will leave the RecordPointer alone but pick up any edits to existing records.

 

[snehal0909]

i have three questions this time.

1) I have a form that is sort of lookup form (users can enter data into that form & that data then will appear in main form as lookup value. WHAT WOULD I NAME THAT FORM?
Is "fdlgIncidentType" proper name? I couldn't find one in naming conventions. -
I read the page you recommended, but i still want to create edit button next to lookup field & that button will bring up form that allows editing records in tblIncidentType.

2)how can i specify the size of a datasheet?
size is going to be w=325 & H=500.
I found the code but don't know where to put it. Below is the code (my for name is fdlgIncidentType)
Forms("frm0").Controls(strForm).Form.Controls(strCtl).ColumnWi dth = intCw
Forms("frm0").Rowheight = 500

3) how do i auto refresh the form so when i add any new data in lookup field, it shows up right away? (i am trying what you've done in the main form - will let you know if it works)

 

[RuralGuy]

This thread is getting a little long in the tooth. How about using your last post to start a new thread. New threads attract more helpers and get fresh ideas from the guru's.

 

[snehal0909]

Thanks Ruralguy!