Dropdown list drops in the wrong place

[RuralGuy]

Anything behind the "New" record button?

[parishpete]

Anything behind the "New" record button?

Nothing.

I've now gone back to basics; I make my two selections from the dropdown lists and click 1 button which enters date, saves record and goes to new record

regards

Pete

[RuralGuy]

Do me a favor and convert the macro to code so I can work on it for you and post it here. http://www.btabdevelopment.com/ts/de...aspx?PageId=91

[parishpete]

Do me a favor and convert the macro to code so I can work on it for you and post it here. http://www.btabdevelopment.com/ts/de...aspx?PageId=91

The only home made macro running on the form is to insert the date (see below). Record save and goto new record are built in ones

Option Compare Database

'------------------------------------------------------------
' Date_out
'
'------------------------------------------------------------
Function Date_out()
On Error GoTo Date_out_Err

With CodeContextObject
.[Month out] = Date
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdRecordsGoToNew
End With


Date_out_Exit:
Exit Function

Date_out_Err:
MsgBox Error$
Resume Date_out_Exit

End Function


regards

Pete

[RuralGuy]

Try this code instead of what you have and see what happens:

Option Compare Database
Option Explicit'------------------------------------------------------------
' Date_out
'
'------------------------------------------------------------
Function Date_out()
   On Error GoTo Date_out_Err
   CodeContextObject.[Month out] = Date
   Const cQuote = """"   'Thats two quotes
   Me.[Loaned to].DefaultValue = cQuote & Me.[Loaned to].Value & cQuote
   DoCmd.RunCommand acCmdSaveRecord
   DoCmd.RunCommand acCmdRecordsGoToNew

Date_out_Exit:
   Exit Function
Date_out_Err:
   MsgBox Error$
   Resume Date_out_Exit
End Function

[parishpete]

The button (command 45) on click code now reads as pasted below but brings up "compile error - invalid inside procedure"

Any thoughts?

regards and thank you for helping

Pete



Private Sub Command45_Click()
Option Compare Database
Option Explicit '------------------------------------------------------------
' Date_out
'
'------------------------------------------------------------
Function Date_out()
On Error GoTo Date_out_Err
CodeContextObject.[Month out] = Date
Const cQuote = """" 'Thats two quotes
Me.[Loaned to].DefaultValue = cQuote & Me.[Loaned to].Value & cQuote
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdRecordsGoToNew

Date_out_Exit:
Exit Function
Date_out_Err:
MsgBox Error$
Resume Date_out_Exit
End Function
End Function



UPDATE

I realised my error and saved the code below separately as a function called by the button click event. I also changed Date_out to Date_lent as there seemed to be an ambiguous conflict.

This gave rise to a different error where ' .DefaultValue = ' was highlighted "; method or data member not found.

Hope this helps

regards

Pete

Public Function Date_lent()
On Error GoTo Date_lent_Err
CodeContextObject.[Month out] = Date
Const cQuote = """"
Me.[Loaned to].DefaultValue = cQuote & Me.[Loaned to].Value & cQuote
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdRecordsGoToNew

Date_lent_Exit:
Exit Function
Date_lent_Err:
MsgBox Error$
Resume Date_lent_Exit
End Function

[RuralGuy]

Type this line by hand:
Me.[Loaned to].DefaultValue = cQuote & Me.[Loaned to].Value & cQuote
...when you hit the "." after Me the intellisense should kick in and show you all of the possible values at this point. Select the correct on and enter another ".". Once again the intellisense should show you the valid possibilities. Is DefaultValue there?

[parishpete]

Type this line by hand:
Me.[Loaned to].DefaultValue = cQuote & Me.[Loaned to].Value & cQuote
...when you hit the "." after Me the intellisense should kick in and show you all of the possible values at this point. Select the correct on and enter another ".". Once again the intellisense should show you the valid possibilities. Is DefaultValue there?

No. Only Value is there following [Loaned to]

regards

Pete

[RuralGuy]

Hmm...what type of control is this?

[parishpete]

Hmm...what type of control is this?

It's an event procedure against on click which runs a subroutine which in turn calls the function you provided.

I am running 2010 with auto update.

Should I be handling this differently? It seemed straightforward at the time.

regards

Pete

As a further thought, having saved the first record, could I pull the field value for [loanedto] from the loan table and put it back into a new record on the form? That record entry will have the highest unique ID number.

[parishpete]

Hmm...what type of control is this?

UPDATED

I'm virtually done thanks to your help. I certainly would have given up without it

The code below uses a variable [Borrower] ( i used a temporary textbox on the form just to show the value of [Loaned to] at any given time).

The code below assigns a default value to [Borrower] which changes each time the button is pressed and passes the value to [Loaned to]. This handles multiple entries perfectly.

I have also set up a second button to handle single entries.

The only thing that needs tidying is error handling in the code below. Because [Loaned to] has an entry and a value in a new record after the button is pressed, I cannot exit the form properly if I press that button last by mistake.

Would welcome your view at your leisure

regards

Pete

Public Function Date_lent()
On Error GoTo Date_lent_Err
CodeContextObject.[Month out] = Date

DoCmd.RunCommand acCmdSaveRecord

Me.[Borrower].DefaultValue = Me.[Loaned to].Value

DoCmd.RunCommand acCmdRecordsGoToNew

Me.[Loaned to].Value = [Borrower]

Date_lent_Exit:
Exit Function
Date_lent_Err:
MsgBox Error$
Resume Date_lent_Exit
End Function

[RuralGuy]

I'm working on another problem right now and will get back to this one when finished, Okay?

[parishpete]

I'm working on another problem right now and will get back to this one when finished, Okay?

Absolutely fine, I'm back in gear now I have it going the right way.

Your help was and is enormously useful.

regards

Pete

[RuralGuy]

I'm one of those guys that thinks a programmer should spend hundreds of hours to save a user a second. I'm not a big fan of a bunch of buttons to choose from if there is a way to make things happen automatically. I think you could eliminate some of these buttons by putting some code in some of the events that occur.

[RuralGuy]

Default values do *not* Dirty a record so setting it would not cause the problem you are describing. You are aware that *all* Dirty records are always saved by simply trying to leave the record in any fashion, right?