Open new form based on this record

[JackieEVSC]

I have a table that used to require the user to enter the ticket number, then clicking on a button would create that record and open that ticket where user enters more info about that ticket.

Now the ticket number is auto-number (I got that part to work with an append query) ... but the code to get the form to open before used the user-entered ticket number (DoCmd.OpenForm "frm_RepairTicket", , , "TicketNum = " & Me.Text60). How do I get it to open the new form based on the auto-numbered field created when I hit the button?

 

[NickHa]

If you are opening the update form from another one, you can use the OpenArgs parameter of the DoCmd.OpenForm action to pass over the record number you want, then use

DoCmd.GoToRecord acDataForm, Me.Name, acGoTo, 1234

to get the record you want (where 1234 is the offset you must calculate). I would suggest that you don't create the autonumber item beforehand, but just start in the data-entry form and use

DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

in your form's Open event to create a new empty record for the ticket. The autonumber will be pre-populated by this action, so your user just fills in the remaining fields.

 

[JackieEVSC]

I'm not sure what I'm doing wrong ... here's the code for the button:

Private Sub Command54_Click()
Dim strSQL As String
strSQL = "INSERT INTO RepairInfo(FirstName,LastName,StudentSite,AssetNumber,SvcTag,EquipDesc,SubmittedBy,School,StudentID) VALUES(" & "'" & Text4 & "'," & "'" & Text12 & "'," & "'" & Text83 & "'," & "'" & Text8 & "'," & "'" & Text14 & "'," & "'" & Text68 & "'," & "'" & Text99 & "'," & "'" & Text83 & "'," & "'" & Text6 & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
DoCmd.OpenForm "frm_RepairTicket", , , "TicketNum = " & Me!TicketNum
DoCmd.Close acForm, "frm_StudentInfo"

End Sub

Previously, the code (which worked) to open the form was ... DoCmd.OpenForm "frm_RepairTicket", , , "TicketNum = " & Me.Text60.

I can create the record, it's just not opening the new form. I know it's a stupid mistake I'm making, but I don't know what to do to fix it! Thanks.

 

[NickHa]

Where is your Command54 button - is it on the frm_StudentInfo form? Also, where are the fields named in your INSERT string (Text4 etc.) located - are they also on that form? And is the form bound to table RepairInfo?
If the answers to these questions is "yes", then why do you use DoCmd.RunSQL to insert the data into the bound table?
My next question is about the DoCmd.OpenForm statement - how does Me!TicketNum relate to the data you've inserted - is this the record key which you previously referred to as Me.Text60?
I'm not clear on the sequence of business events you are trying to create? In your original description, you say the user enters a ticket number, but how does the user know what number to use? Why not just get the user to enter the necessary information before the ticket number is created automatically by the system? Even if you need the user to create the number, just include it in the form with all the relevant associated data in a single form. If you do this, then the ticket number can't be an AutoNumber field.
As an aside, I strongly suggest you use meaningful names for your fields - TextNN has no meaning to anyone and will cause confusion even to yourself. In your example, Text4 relates to FirstName in the RepairInfo table, so name it txbFirstName (txb=TextBox)and call the table field riFirstName (ri=RepairInfo). That way, the relationship is much easier to spot. If the form is bound to the table, the form controls will probably have the same name as the table fields, which is even more obvious!

 

[JackieEVSC]

I realize my code is very amatuerish. Even though I am very new to coding, I do know names should be meaningful and I was lazy. Shame on me.

The Command54 button is on the frm_StudentInfo form. The data entered into the fields on the StudentInfo form is written to the RepairInfo table.

Me.Text60 was where the tech would enter the ticket number. Now, the ticket number will auto-number. We used to use attach tickets printed through this program to pre-printed tickets on card stock. We used the ticket number of the pre-printed ticket, and attached a bar-coded printout on top of it (because the card stock is more durable than a piece of paper).

We have now decided to slip the printout into a clear plastic sleeve, so we need the ticket number to auto-number rather than have the tech enter it.

I have the ticket number field auto-numbering, and clicking the button on the StudentInfo from writes the data to the table ... I can't seem to figure out how to grab the auto-numbered field to use to open the next form.

I have never had any training in vba, so my coding skills are rudimentary at best. I could really use some help with this or direct me to a website that explains it well enough that a non-programmer with a little experience could understand. Thanks.

 

[NickHa]

There are several ways of retrievng the newly-created autonumber key. If you want to grab it from the form in which it's created, you simply reference the control which is bound to the key in the record source. So if your form control is named xxID you can use the form's BeforeUpdate event to capture its value:

Private Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox "Key=" & Me.xxID, vbInformation, "New Record"
End Sub

or you can find the last record in a table using DMax on the key value within the relevant table:

Dim lngMax As Long
lngMax = DMax("xxId", "tblTest")

This works on the basis that the latest record added will have the highest numbered key. This function needs to be executed very soon after the record is inserted, to avoid later updates affecting the result. My preference is for the first method. There are other methods of achieving the same result, but these two should serve your needs from what I interpret of your situation.
On the topic of learning VBA, there are many books on the market. The "VBA Developer's Handbook" is pretty good for explaining the syntax across all VBA contexts. There are similar books for Access developers and you can find tutorials on-line (with simple Google searches) and these focus on the application-specific aspects.

 

[JackieEVSC]

The form is unbound and the auto-numbe field (TicketNum) is populated after the user clicks on a button to create the record, so unless I'm misunderstanding something, I can't use the first option. When we manually entered the ticket number, I was able to use that to open the next form. Now, since it's auto-numbered, I don't know what that is.

The second option sounds like it would work better in our environment, but it would have to be in conjunction with another field, like the asset number (AssetNum), so that it would open the latest record with that particular asset number. Since the units can have been repaired numerous times, I would definitely need the lastest record with the associated asset number. I'll play around with that and see if I can get that to work.

I stopped by the library and got some vba books ... maybe I'll get lucky and find what I need in them! Thanks for your help.

 

[NickHa]

If the ticket number is created in an autonumber field of a record, it implies that the form is bound to the table. Does the creation of the ticket number happen in the past (as in some time before "now")? If not, you should be able to pick the number up using the first method.
As you say, the DMax function will probably do what you need. There is a third parameter which acts as a WHERE clause, so you can set that up something like this:

Private Sub getTopRecord()
Dim lngMax As Long
Dim strTest As String
strTest = "whatever"
lngMax = DMax("ttId", "tTest", "ttCheck='" & strTest & "'")
End Sub

where ttCheck is the field name you want to look at and strTest is the value you want to match. Note the single quote marks on either side of the value - they are needed for string items, but not for numeric items (as in field data type).

 

[JackieEVSC]

I've tried using this code, but I'm not getting something right. Let me try to explain as briefly as I can what I'm trying to accomplish and perhaps you can help me:
I need the maximum ticket number (TicketNum in the RepairInfo table) that has the same asset number (AssetNumber) as the user selected and is stored on this form in a textbox (Text8).
I have tried dozens of variations of your code, but I can't get the next form to open. Where am I going wrong? This is the latest code ... it writes the record to the table, but doesn't open the next form:
Private Sub Command54_Click()
Dim strSQL As String
strSQL = "INSERT INTO RepairInfo(FirstName,LastName,StudentSite,AssetNumber,SvcTag,EquipDesc,SubmittedBy,School,StudentID) VALUES(" & "'" & Text4 & "'," & "'" & Text12 & "'," & "'" & Text83 & "'," & "'" & Text8 & "'," & "'" & Text14 & "'," & "'" & Text68 & "'," & "'" & Text99 & "'," & "'" & Text83 & "'," & "'" & Text6 & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Dim TicketMax As Long
TicketMax = DMax([TicketNum], RepairInfo, [AssetNumber] = Me.Text8)
DoCmd.OpenForm "frm_RepairTicket", , , "TicketNum = " & TicketMax
DoCmd.Close acForm, "frm_StudentInfo"

End Sub