Use VBA to increment field on a continuous form

[amberkei]

I have a form that is being used for sample submission. Ideally, the user will be able to put in some information (3 or 4 fields) and the number of samples that they're taking, click the arrow and it will insert that number of samples into a temporary table that is then displayed on a continuous form. From there, they can edit samples, add more samples, and generate a report to submit to the genotyping group. Once their sample list is final, I will append it to the master list and clear the temporary table.

I've got the basics working. A looped "INSERT INTO" using a counter inserts the relevant information. I cannot, however, seem to get the numbering field to work.

Ultimately, the numbers need to look something like MySample-001, where MySample would be one of the other fields. Incrementing the 001 is the problem. In another situation in the database, I can successfully use DMax+1 to assign a single number on a single form, but I can't get it to increment the sample numbers.

Ideally, I'd like it to happen when the form is generated. However, I'm also open to having them assigned when the sample list is finalized, before inserting into master table.

I'm completely new here, so please tell me if you need more information. I've done a bit of digging around online looking for solutions, but none of them seem to be working for me. After a few weeks now of complete stubbornness, I figured it was time to beg for help.

 

[pbaldy]

Might help to see your code, but if you're already using a counter, you can concatenate, like:

MySample & "-" & Format(Counter, "000")

 

[amberkei]

Please don't laugh at me if it's an ugly mess! ;-)

Private Sub Image22_Click()
'Define variables.
Dim MySQL As String
Dim MyCounter As Byte
Dim Entries As Byte

MySQL = "INSERT INTO TempTable (Planting, SampleDate, SampleResearcher, gene1, gene2, gene3, gene4, gene5)"
MySQL = MySQL & " VALUES (Forms!frmSampling!cboPlanting.Value, "
MySQL = MySQL & " Forms!frmSampling!txtSamplingDate.Value,"
MySQL = MySQL & " Forms!frmSampling!cboResearcher.Value, Forms!frmSampling!ctlgene1.Value, Forms!frmSampling!ctlgene2.Value,"
MySQL = MySQL & " Forms!frmSampling!ctlgene3.Value, Forms!frmSampling!ctlgene4.Value, Forms!frmSampling!ctlgene5.Value)"

Entries = Forms!frmSampling!txtNumberSamples.Value

'Clear out TempTable old data
DoCmd.SetWarnings False
Me.Refresh
DoCmd.RunSQL "DELETE FROM TempTable"

'Insert one line for each sample

For MyCounter = 1 To Entries

DoCmd.RunSQL MySQL

Next
DoCmd.SetWarnings True

'Open TempTable data in form view for editing.

DoCmd.OpenForm "frmTempTable"
Forms!frmTempTable.Refresh
End Sub

So the sample name will be Planting-001.

I'm trying to figure out where I would put in your suggestion.

 

[pbaldy]

The SQL should be built inside the loop so it can incorporate the counter variable. I don't know which field would be affected, but presuming the first, this type of thing:

MySQL = MySQL & " VALUES ('" & Forms!frmSampling!cboPlanting.Value & "-" & Format(MyCounter, "000") & "', "

Note that we are concatenating a value into the SQL string, rather than relying on it being on the form.

 

[amberkei]

What I end up with showing up in my table is the entry "TESTDATA-000" for all of the inserted entries.

Inserted into the field [PlantName], the second field in the list.

Here's the relevant section that includes the changes. I feel like I'm missing something really obvious, like a comma or quote mark or something that's causing the fail. (Sorry, I'm occasionally pulling out field names and the such to avoid having our company lawyer show up at my desk with a stern look on her face and a copy of my NDA.)

MySQL = "INSERT INTO TempTable (Planting, PlantName, SampleDate)"
MySQL = MySQL & " VALUES (Forms!frmSampling!cboPlanting.Value,"
MySQL = MySQL & " '" & Forms!frmSampling!cboPlanting.Value & "-" & Format(MyCounter, "000") & "',"
MySQL = MySQL & " Forms!frmSampling!txtSamplingDate.Value)"

 

[pbaldy]

Sounds like you didn't catch this part:

The SQL should be built inside the loop so it can incorporate the counter variable.

in other words

For MyCounter = 1 To Entries

  MySQL = ...
  DoCmd.RunSQL MySQL

Next

 

[amberkei]

Yes I caught it. Just thought I knew what it meant, and was wrong. That worked for me, and you are officially awesome! Time to transport this from my CodeTest database to the real one!

Gonna bang my head against the wall a bit with my other problems before begging for help again

 

[pbaldy]

Testing?!? We don't need no stinking testing!!

Glad it worked for you.

 

[amberkei]

Nuh-uh. You won't get me there. I've broken too many things to not play around in a tester first.