Drawing value from Excel with vb-script dependant on internal drop down

Robert Saurer

• January 24, 2023 19:53

Hello Bartender world!

I setup a Numberplate label where I choose the voltage in the data entry form from an internal drop down list which is linked to text field Format.Objects("Spannung").Value.

I like to draw the corresponding power consumption from the Excel Datasource which holds the data in rows for the different voltages. (Geraeteetikett_221207.Geraete.Leistung / Leistung USA)

Everything works fine for 220V-240V which is the standard setting, but if choosing 100-120V the power consumption is not drawn from "Leistung USA" but only showing 12345678.

I'm new to vb-scripting! Do I have do place something in OnProcessData? I'm printing to a A4 Laserprinter.

If Format.Objects("Spannung").Value = "220-240 V  50-60 Hz" Then
   Value = Field("Geraeteetikett_221207.Geraete.Leistung")
Elseif Format.Objects("Spannung").Value = "100-120 V  50-60 Hz" Then
   Value = Field("Geraeteetikett_221207.Geraete.Leistung USA")
End If

0

• 

  Peter Thane

  ★ BarTender Hero ★

  • January 25, 2023 09:57

  I think the issue could be one of two things or a combination of both, namely when the VB is being run during the printing process and as you are performing a full textual comparison. 

   I would suggest adding the VB to the text field itself and configuring it as an Event Controlled Script set to (probably) OnNewRecord or else OnPostPrompt so that the VB runs at that time only. You may also want to use the InStr command instead of the full text comparison as this will fail if the pattern doesn't match in full, such as if there is a spurious extra character in the field, such as an extra space.  

  I assume Spannung maybe another field in the database in which case you can amend the 1st line to link to this directly by typing on line 1 Value = and then double clicking on the database field name. For my sample I have just added a drop down list with either the 100-120V.... and the 220-240V.... as selectable at print time which in turn populates a Spannung named field, whilst my database has values of German and USA for the Leistung and Leistung USA fields. 

  My VB is as follows: 

  Value = Format.NamedSubStrings("Spannung").Value

  If Instr(Value,100) > 0 then 
  Value = Field("[VBVoltage].[VBVoltage].[Geraeteetikett_221207.Geraete.Leistung]")
  Elseif Instr(Value,220) > 0 then
  Value = Field("[VBVoltage].[VBVoltage].[Geraeteetikett_221207.Geraete.Leistung USA]")
  else Value = " "
  end if 

  Value = Value

  

  As you can see this is set to look for either the 100 or 220 voltages and from this determines which database field to print.

  I hope this helps

  0
• 

  Robert Saurer

  ★ BarTender Hero ★

  • January 27, 2023 11:19

  Hello Peter,

  thank you very much for pointing me into the right direction. I wasn't so far away, but it still took me 2 days (not full time!) to get it right!

  The clou was to make a named data source "Spannung" and put the vb-script into "OnNewRecord".

  BTW I also had to make a named data source for "Leistung" and "Leistung USA" otherwise I sometimes got an error message.

  I decided to stay with the full text comparison.

  

  Value = Format.NamedSubStrings("Spannung").Value
  if Value = "100-120 V  50-60 Hz" or Value = "3x 200-220 V  50-60 Hz" then
     Value = Format.NamedSubStrings("LeistungUS").Value
  else
     Value = Format.NamedSubStrings("LeistungEU").Value
  end if

   

  0

Please sign in to leave a comment.