populate field with value from same form

[tekboy]

I have a form that generates a number incrementally automatically. This was set by selecting auto number in the table properties. I would like that exact number to be entered in another field as soon as someone tabs into the first field in the tab order. The field to be populated by this process is a key in the underlying table.

I have tried the following vba code with no sucsess.

Private Sub Tally_Number_Click()
If Me!autoTnumber = Not Null Then
Me!TallyNo = Me!autoTnumber
End If
End Sub


My thinking was that when the field with the autonumber is populated, it will not be null and the condiotional statement would then populate the apropriate field with the autonumber.

Suggestions?

 

[jbehrne]

Hi,

Try this instead:
- Set it on your TallyNo's "After Update" event

If Not IsNull(autoTnumber) Then
Me!TallyNo = Me!autoTnumber
Else
MsgBox "AutoTnumber cannot contain a null value!", vbCritical, "Null Value Detected"
Me!TallyNo = ""
Me!autoTnumber.SetFocus
End If

HTH,

jbehrne

 

[tekboy]

unfortunatly, no dice. i double checked the text box names - the text box that is autopopulated is called "autoTnumber". The one I want to have populated is called "TallyNumber". Im using the textbox names.

When I tab into and then out of the textbox in question, nothing happens. Sugestions?

 

[jbehrne]

Hi,

Are you trying to set the values in a new record? If it is a new record that has not been saved then you need to save the record first to generate the autonumber. Try using this code:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

before you pass the value to TallyNumber. If you have a value in the autoTnumber try setting the value on your autonumber's "after update" event. Or, you can try using the bookmark field to save the record and return to that same record and then set the value:

Dim RecNum

RecNum = Me!autoTnumber

Forms!YourForm.RecordsetClone.FindFirst "YourForm.autoTnumber = " & RecNum
Forms!YourForm.Bookmark = Forms!YourForm.RecordsetClone.Bookmark

Me!TallyNumber = Me!autoTnumber

If you have a number in the autoTnumber field then you may be trying to pass a value that is not the same data type for TallyNumber (check to make sure that they are the same data type - i.e. both numbers)

HTH,

jbehrne

 

[tekboy]

jbehrne,

Thats it! the autonumber is obviously a number..the field I am trying to populate is text. Any suggestions on a work around?

 

[jbehrne]

Hi,

You could make your TallyNumber to a number field

OR

there should be a function in VBA that allows you to change the format of one type to another. I can't think of one off the top of my head but VBA help or this forum should have a solution,

HTH,

jbehrne